[thelist] MSSQL: adding integer to time yields date?
Canfield, Joel
JCanfield at PacAdvantage.org
Mon Nov 28 11:53:56 CST 2005
> http://weblogs.asp.net/jasonsalas/archive/2004/07/17/185733.aspx
>
> Looks like the key is working with 'military' time. (The 'feedback'
> portion may be more helpful than the actual post.)
Understood; nothing there directly applied, though.
> As for why you got the 1899 date, ...
>
> ***********************************************************
> Using '00:05:30' to represent 5 minutes and 30 seconds seems
> pretty natural. However, an unfortunate aspect of using the
> datetime data type to store timespans is that you must express
> any interval that is over one day as a specific date, usually
> in 1900. That's why '1900-01-02 10:00:00' is used to represent
> one day and ten hours.
> ***********************************************************
Eh? If I add '4.0' to '8:00PM' (which should look like '1901-01-01
20:00' in a smalldatetime field) how could it go backwards to
12/31/1899? That's two years. Well, at least a year and a day and 20+
hours.
I'm still confused. Here's some SQL data and code:
Query
select StartTime, Length from Events
yields
1900-01-01 20:00:00 4.0
StartTime is smalldatetime, Length is float (not int, as I originally
stated)
This VBScript
datEventEndTime = DateAdd("h",intEventLength,datEventStartTime)
results in 12/31/1899
Oddly, this
datEventEndTime = DateAdd("h",intEventLength * -1,datEventStartTime)
results in 4:00:00 PM, as I would expect. But
datEventEndTime = DateAdd("h",intEventLength * 1,datEventStartTime)
still returns 12/31/1899
So, I'm still confused. And I fully realize it's my lack of
understanding, not y'all's lack of helpfulness.
If you'd like to see the page in question, follow the 'Event Details'
button on the 31st of this calendar:
http://spinhead.com/calendar/index.asp?id=1&year=2005&month=12 (oh
look; everything is centered now. Invisible elves strike again . . . )
Thanks
joel
More information about the thelist
mailing list