My Favourite Data Type (T-SQL Tuesday #136)

My favourite data type is date.

Introduced in SQL Server 2008, the date datatype brought storage of dates in the YYYY-MM-DD format – 0001-01-01 to 9999-12-31, 3 bytes.
This was an addition to the existing types of datetime – 2021-03-09 12:20:16.021, 8 bytes – and smalldatetime – 2021-03-09 12:20, 4 bytes.

It solved a range problem (datetimes can’t store years prior to 1753, smalldatetimes 1900) that previously required hacking around, but so did the new datetime2 type, so that’s not why I’m nominating it my favourite data type for T-SQL Tuesday.

The reason is simply what it is – a date without a time component.

Often we just want a date and don’t care about the time at all, nevermind the time to millisecond accuracy. Think transaction dates, staff joining dates, DOBs, etc. But the existing types foisted a time upon the column along with extra bytes of storage, and I couldn’t begin to count the times I’ve had to type this pattern;

WHERE DateTimeColumn BETWEEN '2020-03-09 00:00:00.000' AND '2020-03-09 23:59:59.999'

-- or if performance is not an issue

WHERE CONVERT(DATE, DateTimeColumn) = '2020-03-09'

With date it’s simple;

WHERE DateColumn = '2020-03-09'

Three cheers for this three part, three byte hero.

Now, I was still at college when date was born and years away from touching SQL. One might think that 13 years later the code I’ve been working with recently as a dev/DBA would have plentiful usage of date when appropriate…

Unfortunately not. This would have been very different if date had been in SQL 2005 or 2000, but it wasn’t – so the result is oodles of datetimes left lying around because the schemas had already been designed.
Then they hang around not only because existing code is extended over the years, but because people doing entirely new deployments copy conventions from existing codebases.

It’s a great example of a ghost of a missing feature still haunting the present. I wonder how many GB/TB (/PB??) on the planet is wasted by datetimes with 00:00:00.000 time components right now.