[dba-SQLServer] What datatype to use for a Time field

Thanks, Susan!

Case 1:
   You have two different columns, date and time (this case makes no sense
to me, but I've seen it so I present it). The FE|BE distinction becomes
important here. If you want to record Date+Time, then do it in the BE in one
column; the FE is another story. You might want to present the date and time
as distinct FE objects, and that's cool, but in the BE record it as one
column.

Case 2:
   You have a column called
PreferredTimeToEndureYetAnotherPhilosophyLecture. Don't record it as a
date/time data type. Model it instead as a number (perhaps using the
proposed model that uses a table called Hours), in which case you're
recording an integer not a date/time object with a meaningless date.

Case 3:
   I am interested in listing all the Horse Riders who have booked their
lesson time as 2pm, regardless of date. This is identical to Case 2, with
just a tad different phrasing. What has worked for me is the lookup table
Hours, with precisely 24 rows. That assumes "on the hour" degrees, and if
you have to grain it more precisely then add more rows, for example 13.00,
13.15, 13.30, 13.45. Either way, this model makes it quite simple to isolate
(query) the people who want their tennis lesson at 13.45 (1:45pm). The FE
can handle the overhead quite easily, and the BE can too.

A.

On 10/8/07, Susan Harkins <ssharkins at gmail.com> wrote:
>
> Nicely done Arthur.
>
> My next question would be -- will the date part ever be used as a date
> component?
>
> Susan H.
>
>