Post reply

excel error

  • December 10, 2009 at 4:55 am

    #139974

    Hi,

    I'm getting a peculiar problem.

    for example i entered 2/2/2010 in excel and integrating it to notepad. itz been interpreted as 40211. if i try to get the date for that number in sql server using

    select datepart(dd,40211),datepart(mm,40211),datepart(yy,40211)

    i'm getting

    (No column name) (No column name) (No column name)

    4 2 2007

    which is not correct.

    how to get the correct dates?

    Thanks,

    Regards,

    Anamika

  • December 10, 2009 at 5:11 am

    #1090455

    select cast(40211 as datetime)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 10, 2009 at 7:26 pm

    #1090953

    Hi,

    while I use cast also i get the same answer.

    I repeat my question :

    for the date '2/2/2010' i got 40211.

    when you use the date functions to get the date I'm getting 4th february instead of 2nd february. is there any reason behind it?

    how can i solve this.

    thanks,

    regards

    anamika

  • December 11, 2009 at 1:46 am

    #1091023

    Excel and SQL Server have different dates for 'Day 0'.

    In Excel, it is 1900-01-00 and in SQL Server it is 1900-01-01. That accounts for one day's difference.

    The other day I am not certain about, but I have read about a slight error in Excel's implementation, in that the year 1900 was interpreted as a leap year, so Excel's days calculation assumes that there was a 29th February in the year 1900 (which there was not). Try typing this date into Excel and you will see that it validates as a date.

    So - that explains both days. You just need to subtract 2 from the SQL Server cast date

    select cast((ExcelDays - 2) as datetime)

    This. of course, stops working if any of year dates are earlier than 1900-03-01, for the reason explained above.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 4 posts - 1 through 3 (of 3 total)

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