[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