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

[Joshua Olson]

> -----Original Message-----
> From: Canfield, Joel
> Sent: Monday, November 28, 2005 7:06 PM
> 
> > Same way as you are currently doing it.  Just don't rely on the
> > day/month/year in the result unless the original time includes a
> > day/month/year portion.  :-)
> 
> Somehow I feel like I'm not being clear enough in my question.
> 
> The day/month/year *is* the result. All it returns is 
> '12/31/1899' - no time portion whatsoever.

What is the EXACT code you are using when you say "All it returns is
'12/31/1899'"  Am I to assume it's something like:

Response.Write datEventEndTime

?

If so, it looks like VBScript does the following when it converts the
datetime to a string:

1.  If the time portion is midnight, then it displays the date only.
2.  If the time portion is non-midnight, then it displays the full date and
time if the date is something other than 12/31/1899 or it displays the time
only if the date is 12/31/1899.

Then you said:

"If you want a date-only field, you populate the date only, and the time
defaults to 0:00. If you want a time-only field, you populate only the
time, and the date defaults to '1901-01-01'

According to SQL BOL, that's just how it works. But it still doesn't
answer my question."

That's how it works on the MSSQL side of the house.  It seems that VBScript
sets the date to 12/31/1899 if you populate only the time.  So, time-only
values coming in from MSSQL server seem to have a different year in the
VBScript than they do in the database field.  

Observation: I just tried to store '20:00' into a smalldatetime field
through Enterprise Manager and I got an error: "The conversion from datetime
data type to smalldatetime data type resulted in a smalldatetime overflow
error."

... then, I did some real testing.  I created a table with two columns, one
datetime and one smalldatetime.  Into each I placed the value of 8pm using
queries.  The first thing I noticed when I viewed the values in EM is that
the datetime column was lacking a date portion, whereas the smalldatetime
showed the date portion.  This immediately told me that for datetime, it can
differentiate between a datetime that has a date portion and one that
doesn't, whereas smalldatetime cannot.  This implied, perhaps, that the
database is storing a NULL value in the first 4-byte portion of the date
field (see Ken's post earlier today entitled "bad sql data" for an
explanation of how dates are stored.)  Since the database has a NULL for the
date portion, it seems to be passing that NULL value to VBScript, which then
assigns it's default date to the missing date portion, which is 12/31/1899.
But, for smalldatetime the database is actually storing 1/1/1900 as the date
portion if it's omitted from the update/insert query, rather than making the
date portion a NULL value.  

So Joel, are you SURE it's a smalldatetime?  I am now lead to believe it's
probably a datetime field.  :-)

NOTE: My statements are based on observations of behavior, and I hope that
somebody (Ken??) can find a definitive resource supporting or refuting my
statements.

Ok, so now to answer your question... which was:

"Is there a simple explanation? Is there a better way?"

So, the answer is no, but at least it makes sense: You're using datetime,
not smalldatetime, and MSSQL uses a different default year than VBScript.
The default display routines for VBScript show only the date portion
(12/21/1899 if year is NULL) of the date when the time is exactly midnight,
or show the time when the time is not midnight and the year is NULL,
otherwise it shows both the date and the time.

So, the second answer is ALSO yes, there is a better way.  Use formatting
functions (FormatDateTime, for example) to ensure that you are in control of
how dates are displayed.

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/