none
Changing Data Types

    Question

  • I have spent almost 5 hours trying to figure out how I can change the data types in the tables. 

    this what I wrote 

    select alter ( [id] as int) as id
    ,[icd9]
    ,if( AgeAtDx ='Null', null, ( cast ([AgeAtDx] as float ))) as AgeAtDx
    ,if( AgeAtFirstDM ='Null', null, ( cast ([ AgeAtFirstDM] as float ))) as  AgeAtFirstDM
    , if ( AgeAtDeath ='Null', null, ( cast ([AgeAtDeath] as float ))) as  AgeAtDeath
    from {HAP_709}.{dbo}.{table_1}

    Anyone can help me  here? please 

    Friday, February 09, 2018 10:19 PM

All replies

  • Do you want to change the data type of the physical columns i.e. change to the schema of the table or you just want to cast the columns in the SELECT query based on null condition (which is what it looks like from your query). If you want the later then you can use ISNULL function to check the datatype. Below is the code you would use:

    select CAST( [id] as int) as id ,[icd9] ,CASE WHEN AgeAtDx IS NULL THEN cast([AgeAtDx] as float ) ELSE [AgeAtDx] END as AgeAtDx ,CASE WHEN AgeAtFirstDM IS Null, cast([AgeAtFirstDM] as float ) ELSE [AgeAtFirstDM] END as AgeAtFirstDM , CASE WHEN AgeAtDeath IS Null THEN cast([AgeAtDeath] as float ) ELSE [AgeAtDeath] END as AgeAtDeath FROM [HAP_709].[dbo].[table_1]


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Friday, February 09, 2018 11:37 PM
  • Do you want to change the data type of the physical columns i.e. change to the schema of the table or you just want to cast the columns in the SELECT query based on null condition (which is what it looks like from your query).

    This is GREAT question Ashish :-)

    Salmanoo, you attempt to solve the issue is so strange and make you question not clear (to me at least)

    SELECT used for selecting data from the table, while ALTER is used for changing the table structure.

    It is not clear how you came to solution that combine SELECT with ALTER.

    If you want to change the table structure then check this link:
    https://www.w3schools.com/sql/sql_alter.asp

    If you want to change the type that the SELECT query return then you can use simple CONVERT function or CAST function as you can see here: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

    If you want the later then you can use ISNULL function to check the datatype. Below is the code you would use:

    select CAST( [id] as int) as id ,[icd9] ,CASE WHEN AgeAtDx IS NULL THEN cast([AgeAtDx] as float ) ELSE [AgeAtDx] END as AgeAtDx ,CASE WHEN AgeAtFirstDM IS Null, cast([AgeAtFirstDM] as float ) ELSE [AgeAtFirstDM] END as AgeAtFirstDM , CASE WHEN AgeAtDeath IS Null THEN cast([AgeAtDeath] as float ) ELSE [AgeAtDeath] END as AgeAtDeath FROM [HAP_709].[dbo].[table_1]

    Ashish, When you use CASE...END then all options must return the same type. There is no sense in CAST only values that are null and leave the values that are not NULL as they are.

    Why not simply use something like bellow

    select CAST( [id] as int) as id
    ,[icd9] 
    ,cast([AgeAtDx] as float) as AgeAtDx 
    ,cast([AgeAtFirstDM] as float) as AgeAtFirstDM
    ,cast([AgeAtDeath] as float ) as AgeAtDeath
    FROM [HAP_709].[dbo].[table_1]


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, February 10, 2018 2:51 AM
    Moderator
  • I have spent almost 5 hours trying to figure out how I can change the data types in the tables. 

    this what I wrote 

    select alter ( [id] as int) as id
    ,[icd9]
    ,if( AgeAtDx ='Null', null, ( cast ([AgeAtDx] as float ))) as AgeAtDx
    ,if( AgeAtFirstDM ='Null', null, ( cast ([ AgeAtFirstDM] as float ))) as  AgeAtFirstDM
    , if ( AgeAtDeath ='Null', null, ( cast ([AgeAtDeath] as float ))) as  AgeAtDeath
    from {HAP_709}.{dbo}.{table_1}

    Anyone can help me  here? please 

    Are you using MS SQL Server?

    The above code doesnt look like Transact SQL one 

    Anyways in SQLServer you will do like this

    select cast( [id] as int) as id
    ,[icd9] 
    , cast (NULLIF([AgeAtDx],'null') as float ) as AgeAtDx 
    ,cast (NULLIF([ AgeAtFirstDM],'null') as float ) as  AgeAtFirstDM
    ,cast (NULLIF([AgeAtDeath],'Null') as float ) as  AgeAtDeath
    from [HAP_709].[dbo].[table_1]

    Beware that if there are any other nonnumeric values in those age fields, then cast will break, so in that case you should use TRY_CAST instead


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Saturday, February 10, 2018 5:53 AM