Date Conversion Issue

• Ganesh Lohani

  Hall of Fame

  Points: 3091

  More actions

  June 23, 2009 at 10:51 am

  #384831

  Hi,

  I have a requirement to extract, transform, and load tables from informix database to SQL Server 2005. I am using Intersolv 3.01, 32 BIT Informix driver. I have a date field . But the filed contains five digit number. For example: 39987, which is equivalent to 06/23/2009.

  While running the package, I get the error:

  The date could not be converted to mm/dd/yyyy format.

  How can I convert that by using T-SQL code or in SSIS package.

  Thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 23, 2009 at 11:30 am

  #1014127

  Looks like this will do your conversion: select dateadd(dd, 39987, -2).

  Replace the 39987 with the fdate field in your table.

• MannySingh

  SSChampion

  Points: 12465

  More actions

  June 23, 2009 at 11:31 am

  #1014128

  I see it is a Julian date.. if i am right?

  there are many functions to convert from julian to georgian..

  http://www.databasejournal.com/features/mssql/article.php/1442021/Working-with-Dates.htm

  search for julian in here:

  Maninder
  www.dbanation.com

• Ganesh Lohani

  Hall of Fame

  Points: 3091

  More actions

  June 23, 2009 at 1:27 pm

  #1014217

  Lynn and Mani,

  Thank you so much for your reply.

  I got it.

  Thanks

  Ganesh