Post reply

date pasted as 1/2/1900 in excel instead of 1/1/1900

  • April 1, 2009 at 12:19 am

    #94750

    Ihave dates in an SQL database represented as 1/1/1900. When I generate

    a table query and paste the data from the SQL query into excel USING UI the

    1/1/1900 dates are pasted into excel as 1/2/1900.

    Is there any reasons for this increment?

  • April 1, 2009 at 12:47 am

    #969642

    Hmm, doesn't happen to me. Can you post an example that we can use to reproduce it?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 2, 2009 at 6:58 am

    #970615

    Like RBarryYoung, I can't replicate the behaviour you describe between SQL 2005 Management Studio and Excel 2003.

    However, I can think of two possible sources of confusion here.

    When represented as an integer, Excel displays 01 January 1900 as 1. SQL Server displays it as 0. Perhaps somehow your dates are being identified by Excel as numbers, and subsequently being converted to dates?

    For dates after 28 February 1900, this difference increases to 2, because of a bug in Excel which mimics a bug in Lotus 1-2-3, wrongly identifying 1900 as a leap year: http://support.microsoft.com/default.aspx?scid=kb;en-us;214058

  • September 1, 2009 at 5:43 am

    #1047422

    hi,

    Even i have the same problem? When i import 1/1/1900 date in Excel it changes to 2 jan 1900 , takes 1 day more.

    Do you got the solution.

  • September 3, 2009 at 4:44 am

    #1048643

    Anybody??

  • September 3, 2009 at 6:58 am

    #1048688

    I believe that eHarper is onto the key to this problem. There is a notorious "feature" of SQL Server and Windows in that their Day Zeros are one day off. The natural consequence of this probably is that if you import the dates in numerical form and convert them back to dates, they will probably be off by one day.

    The obvious solution to this would be to export and import the dates in textual form instead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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