Post reply

Date conversion problem

  • January 14, 2014 at 11:49 pm

    #284450

    Hi,

    My source is CSV file and date field in the csv is like - 40511. ( this resembles to date 11-29-2010.)

    But not able to load into sql server table. I am facing issue to convert this field to date field.

    Please suggest idea how can i aschive this or how can i convert particular column in csv file to date column in SSIS.

    Kindly Help.

    Thanks

    Abhas.

  • January 15, 2014 at 12:58 am

    #1680142

    This is just stab in the dark, but I am guessing that 40511 is some Julian type date. With a little date math, I came up with:

    select cast(DATEADD(dd, 40511, '1899-12-30') as DATE)

    See if it works for you.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 15, 2014 at 7:05 am

    #1680254

    LinksUp (1/15/2014)

    guessing that 40511 is some Julian type date

    40511 is the serial date in excel for 29/11/2010

    Excel holds dates as days from 01/01/1900 (where 01/01/1900 = 1)

    So your method of conversion is correct 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 15, 2014 at 7:35 am

    #1680263

    If these are dates coming from Excel, be careful if your data has very old dates.

    Excel sees the years 1900 as a leap year (a legacy from Lotus 1-2-3), so you would have to adjust your calculation for dates before 1900-03-01.

    http://support.microsoft.com/kb/214326/en-US

  • January 15, 2014 at 9:16 am

    #1680331

    Thanks LinksUp

    It is working for me . Thank you so much!

    :-):-):-)

    Thanks

    Abhas

  • January 15, 2014 at 10:21 am

    #1680354

    Glad it is working for you!

    Thanks for letting us know the result.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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