No, no.... you were spot on. TIME columns are ok for durations of 1 day but aren't actually duration columns. I also have a hard time understanding why people would want to separate date and time even for display purposes unless they are, in fact, just trying to display duration. Certainly, I wouldn't store date and time separately.
I might store just the date if it's a column guaranteed to only ever need to be a whole date but then there's the problem of doing nasty little conversions like the one on this thread. For example, you asked if I was suggesting the following...
2012-12-21 05:00 + 2012-12-24 07:00
If the second date/time is supposed to be the duration and the first date/time is the start date, then kind of but not quite. If someone worked 1 hour, 13 minutes, and 59 seconds, then any of the following would work just fine to get the EndDate...
2012-12-21 05:00 + '01:13:59'
2012-12-21 05:00 + '1900-01-01 01:13:59' --Admittedly, confusing, but shows how things work.
StartDate + Duration -- Where both are data/time datatypes and the duration is stored as a result of (for example) '1900-01-01 01:13:59' .
Yeah... I know this stuff doesn't work for any of the "new" date/time datatypes. I think that MS really and unnecessarily made it a whole lot more difficult to do such simple things as adding a simple duration to a starting date and time. I wish they would have (no pun intended) spent the time making a proper "Duration" datatype that would allow you to store a (for example) 49 hour duration as something a little easier for folks to figure out other than 1900-01-03 01:00:00. For example, the following DOESN"T currently work...
SELECT Duration = CAST('49:00:00' AS DATETIME)
Instead, you have to go through a bunch of hooie to parse the hours, minutes, and seconds and then DATEADD each of those back to "0" (1900-01-01).
Same goes the other way around. Using subtraction between a start and end date/time is easy and accurate even across years. With the new date/time data types, you have to do something stupid like doing a DATEDIFF in milliseconds and then a DATEADD to "0" to reconvert it back to a date/time data type.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs