Post reply

Convert Datatype of Existing Data from Char to Date/time with SQL

  • May 23, 2011 at 1:15 pm

    #250695

    Hi,

    I am relatively new to SQL Server and am trying to update the datatype of about 3000 records from a Char to Datetime so I can use the Datediff function. I created a view that achieves the conversion but what I think I need to do is alter the data in the origin table.

    Select CONVERT(datetime, CONVERT(char(8), TRANS_ACCOUNTINGDATE_ALLCAMPAIGNS_2010_ALLPROCESSINGACCOUNTS_ALL)) from Accounting;

    What I think I need to do is an alter table and iterate over each row performing the conversion. Trying to change the data type using the GUI is not working for me.

    Any help would be appreciated.

    Thanks

  • May 23, 2011 at 1:27 pm

    #1328678

    Why doesn't the GUI work? Is it generating an error?

    The T-SQL code to run is:

    ALTER TABLE <YourTable> ALTER COLUMN <YourColumn> DATETIME <NULL?>

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 23, 2011 at 1:47 pm

    #1328690

    Thanks, I got it!

  • May 23, 2011 at 2:25 pm

    #1328707

    Would you mind sharing with us so that we (and anyone that stumbles across this in the future) know what actually worked for you?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 23, 2011 at 2:55 pm

    #1328726

    Sure,

    When I changed the field to datetime in the GUI in SSMS, it gave a warning that there may be some data loss but allowed me to proceed. I then had to refresh the table by right clicking it and going to "refresh".

    My final mistake was that when I was using the datediff function, I had:

    select datediff(day,'accountingdate','paiddate') as datedifference from accounting;

    The problem here was the single quotes around my variables which was making sql interpret them as strings. Once I removed the '' everything was fine.

    Thank you again for your help!

    -Danny

  • May 23, 2011 at 3:29 pm

    #1328746

    Thanks for the feedback.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply