Dates and Times in SQL Server: more functions you should never use

Previously we looked at four built-in functions to get the current date and time in SQL Server and Azure SQL Database using Transact-SQL (T-SQL).

We identified that out of the options provided, SYSUTCDATETIME() is the recommended method because it relies on Coordinated Universal Time (UTC), and uses the DATETIME2 data type which has a much higher accuracy than the DATETIME data type.

Remember that functions in T-SQL take zero or more parameters, so brackets are required, but this also makes them easier to visually identify in code. In some text editors (including SQL Server Management Studio), the system functions may even be highlighted in a specific colour.

This week we’ll look at several more date and time functions in T-SQL that we should never use.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP is the ANSI-equivalent of GETDATE(). ANSI is an acronym for the American National Standards Institute, and sometimes vendors will include ANSI functions in their products so they can say that they’re ANSI-compliant (which is not a bad thing, in most cases).

There are three main problems with CURRENT_TIMESTAMP:

• No brackets. It goes against the rules about functions. So much for standards!
• It’s functionally equivalent to GETDATE(), which uses DATETIME, which we previously identified is old and bad.
• It’s too similar to the poorly-named TIMESTAMP data type, which has nothing to do with dates and times and should be called ROWVERSION.

Bottom line: don’t use CURRENT_TIMESTAMP.

DATETIMEFROMPARTS()

Imagine if we had the individual components of a date and time (i.e. year, month, day, hour, minute, second, millisecond), and wanted to combine them into a single data type. The good news is that we can! The bad news is that we shouldn’t use DATETIMEFROMPARTS().

The syntax is fairly straightforward:

DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)

However, as we know, the DATETIME data type returns a value to the nearest three milliseconds, as long as it ends with 0, 3, and 7. What is the use of a data type that returns data with different result to what we expect? This is data corruption by definition.

Instead, we should use the DATETIME2FROMPARTS() function, which requires one additional parameter, but is all the better for it. The syntax is almost the same:

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)

Keeping in mind that we can save one byte per column by switching from DATETIME to DATETIME2(3) and have better accuracy, consider the following example:

-- Declare and assign DATETIMEFROMPARTS function to a variable
DECLARE @dt DATETIME = DATETIMEFROMPARTS(2018, 5, 16, 9, 8, 17, 321);

-- Declare and assign DATETIME2FROMPARTS function to a variable
DECLARE @dt2 DATETIME2(3) = DATETIME2FROMPARTS(2018, 5, 16, 9, 8, 17, 321, 3);

SELECT
@dt AS [DATETIMEFROMPARTS],
DATALENGTH(@dt) AS [@dtBytes],
@dt2 AS [DATETIME2FROMPARTS],
DATALENGTH(@dt2) AS [@dt2Bytes];

Note: DATALENGTH() is a T-SQL function that returns the number of bytes from the parameter that is passed in.

The results speak for themselves. Using DATETIME2FROMPARTS() we avoid silent corruption of our input data, and also consume less space:

Bottom line: if we’re going to make up a date and time from constituent parts, use DATETIME2FROMPARTS() instead of DATETIMEFROMPARTS().

ISDATE()

Finally for this week, here is another built-in T-SQL function we should never use. ISDATE() is meant to return a boolean value (1 if true, or 0 if false) if the parameter we pass in is a valid date. The first issue is that instead of using the BIT return data type (which is only one bit wide), for some reason it uses the INT data type (four bytes wide) which is 32 times wider than it needs to be!

Here are two more problems with ISDATE():

• It returns true if we pass in a valid TIME parameter. Shouldn’t this be ISTIME() instead?
• It returns false if we pass in a valid DATETIME2 parameter.

If you’re confused right now, you’re not alone. This is from the official documentation:

Returns 1 if the expression is a valid date, time, or datetime value […and…] returns 0 if the expression is a datetime2 value.

But wait, that’s not all. Further down the page it talks about deterministic values for ISDATE() and how the return value is dependent on language and session settings.

We’ll get into more detail about these issues later in this series, but the takeaway here is that even if we have what we think is a valid DATETIME (which we should stop using anyway), DATE, or TIME (this one still gets me), our server or session configuration settings can cause this function to return unexpected results.

Bottom line: don’t use ISDATE().

If you have any date and time functions you’d like to complain about or would like me to cover in a subsequent blog post, find me on Twitter at @bornsql.

Photo by MIOPS Trigger on Unsplash.