April 1, 2009 at 12:19 am
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
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
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
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
Anybody??
September 3, 2009 at 6:58 am
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