SDU Tools: Find columns that shouldn't have time

Ever since I started working with SQL Server back in 1992, the #1 requested feature that I kept hearing about was a separate date data type. SQL Server had a smalldatetime and a datetime but these included both date and time within the same type.

Having date and time often led to odd bugs or performance issues, where people didn’t realize that times were included and tried to work with the values as dates.

In SQL Server 2005, the date data type was meant to be the poster-child for the new SQL CLR integration and there was a shiny new date data type built using it. But when we (the MVPs) looked at it, we all disliked it immensely. The main problem was that it didn’t work with all the existing date-related functions. So the date data type was removed from the product before it shipped.

In SQL Server 2008, we got new intrinsic (standard built-in) data types for date and time (and a few interesting other ones), and they worked with the date-related functions. This was what was needed.

However, there’s a lot of code out there that was built before this data type existed, or was simply built with the wrong data type.

As part of our free SDU Tools collection, we have provided two ways to find columns that might be worth changing:

ListPotentialDateColumns looks at the names of the columns. If the names suggest that it’s a date, but the data type is one that includes time, it’ll flag the column as a potential. You can see it being used here:

In each case, the column name suggests that it’s a date, but the data type says otherwise. That runs quite quickly as it’s just looking at the column names and data types.

Harder to find are columns that are declared with a datetime, smalldatetime, datetime2, etc. yet don’t contain any time values. To make these earlier to locate, we’ve built another tool.

ListPotentialDateColumnsByValue looks through all the values in a column. If the column only contains dates, it reports it:

Now that’s obviously a bit slower as it has too look through all the data. After running this (for example), we know that the ValidFrom column in Sales.BuyingGroups only contains dates, without any time component. It’s likely that it should have been a date column instead.

You can see both of these in action here:

You’ll find more information on our free SDU Tools here:

http://sdutools.sqldownunder.com

2018-01-17