DateTime Conversion

• kfschaefer

  SSC Eights!

  Points: 838

  More actions

  November 12, 2004 at 5:24 pm

  #165445

  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?

  Thanks,

  Karen

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  November 13, 2004 at 1:26 pm

  #530031

  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
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  November 15, 2004 at 7:36 am

  #530102

  I suggest beginning your code with:

  SET DATEFORMAT YMD

  <rest of code>

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

  -SQLBill