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

[Phil Turmel]

Canfield, Joel wrote:
>>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.
> 
> I can't see how that's a meaningful answer to the math problem '8:00PM'
> + 4.0. 
> 
> The original time will always include a day/month/year portion because
> MSSQL only has two date/time data types, 'datetime' and 'smalldatetime'.
> 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.
> 
> I'm not trying to be difficult; really I'm not. I'm just confused.
> 
> joel

Joel,

Let me kick in my 2c...

It helps to think of datetime types in VB-ish languages as whole number 
days, with fractions representing the time of day (actually is stored 
this way on some flavors).  When no format is specified, the 'leading 
zeros' or 'trailing zeros' are trimmed.  In this case, for some 
rollover-related reason, the "zero" date is 1899-12-30.  Your case would 
be similar to 0.8333333 + 0.1666666 = 1.

Try outputting your result via CONVERT(CHAR(5), yourresult, 108).  You 
should get 00:00.

Details on CONVERT here:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

HTH,

Phil