Post reply

Converting Excel Date to sql Date Format

  • May 27, 2011 at 1:38 am

    #241154

    My problem is kind of weird

    In our project we mainly write data from excel to sql-server tables

    The date in the excel sheet is in excel's propriety date format i.e 40630.38042 which is equivalent to 03/28/2011 xx:xx:xx(some date time value)

    Now the problem is that we were using the code to convert this decimal date value to varchar value 03/28/2011 using this formula

    CONVERT([varchar](10), dateadd(day,-2,convert(decimal,[orderdate])),(101))

    orderdate here is in this format - 40630.38042

    Now after 6 months of uploading hundreds of excel sheets we realized that the dates were not getting converted right

    the time part after the decimal place was messing up this logic - and we don't even need time in our database

    so I tried CONVERT([varchar](10), dateadd(day,-2,convert(decimal,substring([orderdate],1,5))),(101))

    and it seems to work fine

    Now to apply the correct formula I first need to change them back to the original decimal date format

    so basically I need a reverse formula for

    CONVERT([varchar](10), dateadd(day,-2,convert(decimal,[orderdate])),(101))

    Is there a way to do this??

    If not I'll have to upload all the data (100+ excel sheets again) - which will not make my clients very happy 🙁

    Thanks,

    Kavita

  • May 27, 2011 at 4:22 am

    #1330863

    Can you provide an example to work with?

    i.e.

    a) Value in Excel format

    b) Date value you expected

    c) Incorrect Date value currently in SQL

  • May 27, 2011 at 4:38 am

    #1330871

    select CONVERT(numeric(18,4),getdate(),101);

    Update Table set datecolumn = CONVERT(numeric(18,4),datecolumn,101);

    Note

    First take backup your database and test it on sample data first then come to production Server

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • May 27, 2011 at 4:40 am

    #1330873

    Hi Shark,

    Declare @OrderDate varchar(100)

    --Original DateTime from Excel

    set @OrderDate = '40441.50551'

    select CONVERT([varchar](10), dateadd(day,-2,convert(decimal,@orderdate)),(101))

    select CONVERT([varchar](10), dateadd(day,-2,convert(decimal,substring(@orderdate,1,5))),(101))

    Results

    09/21/2010 --Incorrect

    09/20/2010 --Correct

  • May 27, 2011 at 4:56 am

    #1330880

    Seems to have rounded them all up by 1 day due to the time on the end? (some are probably ok though?) (confirm this)

    If so it may be safer to just do a comparison against the date column and the correct calc to see which ones are wrong, then just update them with a dateadd -1 d (I can provide this if you can confirm the first question)

    I tested the code provided above by Syed but had issues getting it to convert back and if it did it came out as 0000.0000?

  • May 27, 2011 at 5:00 am

    #1330884

    Shark,

    You are absolutely right. Some Values are converted correctly and some aren't.

  • May 27, 2011 at 5:01 am

    #1330885

    And Yes the difference is always of 1 day

  • May 27, 2011 at 5:28 am

    #1330900

    Just realised my method is no good as you can't work out which ones were wrong in the first place without reloading your sheets!

    Will get back to you.

  • May 27, 2011 at 5:30 am

    #1330902

    Whats the impact on business reporting of the order date being out 1 day? Some monthly reports would show up orders that shouldn't be there and should be in previous month...thats it though?

    From a practical level I'm wondering of the benefit of trying to fix an issue that has left "order date" out by 1 day on just some orders.

    EDIT: And to clarify - you have an impossible task getting them back and then converting again because in storing the value to begin with you have lost the time part of the excel format therefore you can't get it back to the value it originally was, you therefore cannot then apply that through the correct conversion to get the value you need...

    Run this to see what I mean

    Declare @OrderDate varchar(100)

    , @Date1 datetime

    SELECT '40441.50551' AS OriginalExcelDate

    set @OrderDate = '40441.50551'

    set @date1 = (select CONVERT(datetime,CONVERT([varchar](10), dateadd(day,-2,convert(decimal,@orderdate)),101),101))

    select @date1 AS OriginalSQLValue

    -- Retrieved SQL DateTime in Excel format

    set @orderdate = (select CONVERT(numeric(18,4), @Date1, 101))

    SELECT @OrderDate AS NewExcelValue

    select CONVERT([varchar](10), dateadd(day,-2,convert(decimal,substring(@orderdate,1,5))),(101)) AS NewSQLValue

  • May 27, 2011 at 6:32 am

    #1330930

    Shark,

    1 day wont make any difference if the month is the same. Most of our reports are generated on monthly basis so now that I am sure the date is always messed up with by one day, I just need to reload the excel sheets that have data for weeks that overlap between two months like 9/27 - 10/1.

    This way I'll have to upload just 20 files instead of 200+ files..

    It does make my work a lot easier.

    Thanks a lot for your time 🙂

  • May 27, 2011 at 7:25 am

    #1330974

    No problem.

    My advice now would be - load the sheets into holding table first and then work out which records you are replacing so you don't end up with missing records!!!

    Good luck 🙂

  • May 27, 2011 at 9:04 am

    #1331053

    Thanks.

    I'll keep that in mind 🙂

  • January 22, 2012 at 3:50 am

    #1436873

    Hi

    Really thankful for the explanation. It makes my day very easy complet all these task. This code was really very much helpful. I dont have words to say thanks to u....

    Regards

    S.Siraj

Viewing 13 posts - 1 through 12 (of 12 total)

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