DateTime Conversion

  • I currently, have a field in a table that is a numeric field = 20030104 - I need to convert this to a date field = 01/04/2003 - what is the proper syntax for use in a select statement?

    Note that I am creating a table and I tried to change the field type to datetime, however got an error on Arthimetic overflow - the original data is coming from directory files info.

    Which is my best option changing format on the created of the table or on the select statement?



  • If you have the option to change the underlying data type in the table, you should do so. That way you avoid many problems that might come. In a SELECT statement you can work around with something like this:

    declare @a int

    set @a = 20030104

    select cast(cast(@a as varchar(20)) as datetime)



    2003-01-04 00:00:00.000

    (1 row(s) affected)

    Note, that this result is based on my settings. It might differ for you, but your desired result in merely a presentational issue anyway.

    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

  • I suggest beginning your code with:


    <rest of code>

    Setting the dateformat tells SQL Server how to interpret the date value you are providing.


Viewing 3 posts - 1 through 2 (of 2 total)

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