[thelist] MSSQL: adding integer to time yields date?

Ken Schaefer Ken at adOpenStatic.com
Tue Nov 29 03:06:51 CST 2005


Hi Joel,

I'm unable to replicate the behavior you are seeing. I have a table with the
following values:

DateTime				Int
1/1/1900 8:00:00 PM		4
1/1/1900 8:00:00 PM		6

I extract the two records and do a loop. For each record I do a DateAdd, and
print the result. The resulting values are:

date = 1/1/1900 8:00:00 PM
interval = 4
1/2/1900

date = 1/1/1900 8:00:00 PM
interval = 6
1/2/1900 2:00:00 AM

This comes from the following code:

<%
Do While Not objRS.EOF

	dteDate = objRS.Fields(0).Value
	intInterval = objRS.Fields(1).value


	Response.Write("date = " & dteDate & "<br />")
	Response.Write("interval = " & intInterval & "<br />")
	Response.Write(DateAdd("h", intInterval, dteDate))
	Response.Write("<br /><br />")

	objRS.moveNext

Loop
%>

So, it appears that if your time addition results in a time that's exactly
midnight, the 00:00 gets dropped (resulting in a date only). But if your time
addition results in any time other than midnight, the time portion is
present.

I'm not entirely sure why your date is going backwards however. The only way
I could think of that this is happening is that the date portion is somehow
missing in ASP, and so it's using its own base date...

Cheers
Ken



-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Canfield, Joel
Sent: Tuesday, 29 November 2005 4:54 AM
To: thelist at lists.evolt.org
Subject: Re: [thelist] MSSQL: adding integer to time yields date?

> 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