Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Faustius
Starting Member
2 Posts |
 Posted - 2012-10-31 : 19:27:01
|
| Hi all, I'm ODBCing to a 2008 SQL Server that stores datetimes as the number of minutes since 1/1/1900 in a dec(15,1) field called stop_moment (not my choice). For example, 9/22/2012 06:00:00 would be stored as 59290920.0. In the past I've been converting to datetime by simply dividing by the number minutes per day (1440), which for our example yields 41174.25, which MS Access and Excel can format correctly as a datetime. However, now I need that number converted to a datetime within sql server, and convert(datetime,41174.25) yields 9/24/2012 06:00:00, which is two days off. I guess my question is why is that, and would it be safe to simply always subtract 2 days when converting (convert(datetime,(stop_moment/1440.0)-2)? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-31 : 19:59:46
|
| Actually the reference date (i.e. the date represented by the number 0) used in Excel/Access is December 39, 1899. So 9/22/2012 6:00 AM is 59290920 minutes from December 39, 1899 rather than January 1, 1900. Reference date in T-SQL is January 1, 1900.So your method of solving the problem - namely subtracting two days is correct. |
 |
|
|
Faustius
Starting Member
2 Posts |
Posted - 2012-11-01 : 17:17:54
|
| That's exactly what I needed to know. Thanks so much for the quick response! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-01 : 19:30:42
|
| You are welcome, and glad you were able to figure it out even though I was telling about December 39, 1899 :) |
|
|
|
|
|
|
|
|
Numeric to Datetime question