| Author |
Topic |
|
ronstone
Starting Member
32 Posts |
 Posted - 2003-06-18 : 13:06:48
|
| Example: When I convert a date to serial in Excel, 6-1-2003 returns 37773.When I run:SELECT CONVERT (datetime, 37773) I get 6-3-2003. Why does it add two days?Thanks,Ron |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-18 : 13:20:27
|
| How does 37773 get converted in the first place to datetime? If I use 37771 instead of 37773, I get 6-1-2003. Are you sure that 37773 is supposed to convert to 6-1-2003? And if you are, how can I do the conversion by hand?Tara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-18 : 13:27:15
|
| It doesn't "add" two days, Excel and SQL Server have different methods for performing date conversions, and an integer value doesn't translate identically between systems. You should not rely on integer value conversions in SQL Server, although it does store dates as numerics, as you can see it's not consistent with Excel. Use some form of mm.dd.yyyy or yyyymmdd format when passing dates to SQL Server. |
|
|
|
ronstone
Starting Member
32 Posts |
Posted - 2003-06-18 : 13:31:03
|
quote:
How does 37773 get converted in the first place to datetime? If I use 37771 instead of 37773, I get 6-1-2003. Are you sure that 37773 is supposed to convert to 6-1-2003? And if you are, how can I do the conversion by hand?Tara
It's *supposed* to be the number of days since 1/1/1900 |
|
|
|
ronstone
Starting Member
32 Posts |
Posted - 2003-06-18 : 13:35:53
|
quote:
It doesn't "add" two days, Excel and SQL Server have different methods for performing date conversions, and an integer value doesn't translate identically between systems. You should not rely on integer value conversions in SQL Server, although it does store dates as numerics, as you can see it's not consistent with Excel. Use some form of mm.dd.yyyy or yyyymmdd format when passing dates to SQL Server.
Thanks for the quick response Rob. Unfortunately, the application that writes to the sql db stores the date in serial form only...Since it's the # of days since 1/1/1900, would it be safe to subtract two? In theory at least....I can't imagine there isn't a total sql solution!-Ron |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-18 : 13:38:38
|
| The difference between Jan 1, 1900 and Jun 1, 2003 is 37,771. And the difference between Jan 1, 1900 and Jun 3, 2003 is 37,773. Excel isn't doing the conversion correctly.Here is the code to test it out in Query Analyzer:SELECT DATEDIFF (DAY, 'jan 1 1900', 'jun 1, 2003')SELECT DATEDIFF (DAY, 'jan 1 1900', 'jun 3, 2003')For information about DATEDIFF, please see BOL.Tara |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-06-18 : 16:07:21
|
| Excel date serial values are a bit screwed up. Microsoft would blame Lotus 1-2-3, no doubt.Given an Excel serial value that represents a date from 19000301 onwards, you can subtract 2 to get an integer that SQL Server will convert to datetime for the same date.Given an Excel serial value that represents a date between 19000101 and 19000228, you can subtract 1 to get an integer that SQL Server will convert to datetime for the same date.Given an Excel serial value of 60, you should panic, since this represent the fictitious date of 19000229.Given a negative number, subtracting 1 ought to work, but the latest version of Excel doesn't like treating negative numbers as dates.Edit: Argh, wrong sign!Edited by - Arnold Fribble on 06/19/2003 06:36:08 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-18 : 19:34:26
|
| Did I mention I hate MS....Hey ya still gotta eat (and the family too).Still, its easier than DB2 (or is that UDB..when will they get that straight) OS/390...whats that you ask?Thanks for the insight...it will(more than likely, most inevitable) become useful..USER: "What? Excels not an enterprise platform"Don't even want to mention Access...damn it still slipped in...Brett8-)Edited by - x002548 on 06/18/2003 19:37:08 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-06-18 : 22:41:37
|
| Anyone ever wonder if Arnold majored in math in ages past?Sam |
|
|
|
|
CONVERT Serial Date adds 2 days??