CONVERT DateTime from ISO format

• Richard D. Cushing

  Ten Centuries

  Points: 1296

  More actions

  September 8, 2005 at 10:02 am

  #165968

  In an attempt to do some data conversion, I am working with a source table where the some ISO-formatted dates are stored as FLOAT values (e.g., today would be seen as 20050908.0).  I am trying to convert these to 'mm/dd/yyyy' or 'yyyy/mm/dd' formatted dates and store them as DateTime data in another table.

  When I attempt to use...

  CONVERT(DateTime,Entered_Dt,112)

  I get an arithmetic overflow error.  I assume this error stems from some failure of SQL to do an implicit conversion from FLOAT to the ISO DateTime format (112).

  I have been able to do the conversion using this code:

  SELECT 

     SUBSTRING(CONVERT(Char(8),CAST(Entered_Dt AS Dec(10,0))),5,2)

    + '/' +

     RIGHT(CONVERT(Char(8),CAST(Entered_Dt AS Dec(10,0))),2)

    + '/' +  

     LEFT(CONVERT(Char(8),CAST(Entered_Dt AS Dec(10,0))),4)

         AS CreateDate

  However, it seems to me that there ought to be a more direct route than this.  Any ideas?

  Thanks for your help.

  
  Richard D. Cushing
  Sr. Consultant
  SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 8, 2005 at 10:08 am

  #588448

  This seems to work

  Declare @a as float

  set @a = 20050908.0

  select cast(cast(cast(@a as int) as varchar(8)) as datetime)

• Richard D. Cushing

  Ten Centuries

  Points: 1296

  More actions

  September 8, 2005 at 12:55 pm

  #588517

  Thanks, I'll give it a try.

  
  Richard D. Cushing
  Sr. Consultant
  SYNGERGISTIC SOFTWARE SOLUTIONS, LLC