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

Canfield, Joel JCanfield at PacAdvantage.org
Mon Nov 28 14:14:44 CST 2005


> > 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.  
> > ***********************************************************

Following an offline nudge from RonL:

> Or try 3.99 hours and see if that gives the right answer?

As long as the addition ends short of midnight, it gives the expected
answer. But if the gig ends at midnight, it ends at midnight, not 11:59.
Gr.

Of course, if my addition ends after midnight, we're back to 1899, but
at least it displays the time ;)

Think I'll fool around with it a bit more, but I'm surprised no one's
written some simple definitive treatise on this already. I'm certainly
not the first person to ever add four hours to eight in the evening in a
web page ;)

joel



More information about the thelist mailing list