[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