Hi skjoldtc,

SQL Server 2008 adds several new data types, but doesn't change the way the current datatypes ([font="Courier New"]datetime[/font] and [font="Courier New"]smalldatetime[/font]) work. And everything considered, that is a good thing - you don't want your existing code to break when upgrading, right?

Many of the issues discussed here apply to the new data types ([font="Courier New"]date[/font] and [font="Courier New"]time[/font], but also [font="Courier New"]datetimeoffset[/font] and the hideously named [font="Courier New"]datetime2[/font]) as well, though obviously not the weird 1/300 second precision - the new datatypes that include a time portion all allow the user to specify 0 to 7 digits of fractional second precision.

As Lynn says, searching for a particular day becomes easier with the [font="Courier New"]date[/font] datatype, as this is now one single value so an equality search can be used (though you still will get correct results when using the range search technique). For all other datatypes, I would still continue to use the half-open interval search outlined earlier in this discussion, as it will always work even when the datatype later changes, and it saves me research time. Sure, I can save myself some typing by first checking the datatype ([font="Courier New"]datetime2(5)[/font] - so that is 5 digits after the decimal) and then using BETWEEN with an end datetime value of '2009-01-31T23:59:59.99999' - but I save myself a lot more time by not having to check the datatype at all and just using < '20090201'. (And since the default time part is still midnight, I can omit the time portion in this case).

There might or might not be other, new issues with the new date/time datatypes, but I haven't played with them quite enough to find them.