The first time I read about the new Date and Time datatypes I though “Well, that’s nice”. But the more I think about it the happier I am. No more stripping off the time values to do a date only comparison. Thank goodness. Some other pluses; possible use of indexes, safe to use BETWEEN for date ranges, easier reading of code without all of those CONVERT functions.
But don’t be so eager to leave out the time part. You need to be absolutely sure that you don’t need the time piece. If your not sure, keep the time part in a column separate from the date.
There is some good sample data for these datatypes in the AdventureWorks2008 database. Take a look at the Human Resources.Employee table, Birthdates & Hire Date columns. And also look at Human Resources.Shift table, Start Time & End Time columns.
