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 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
- 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
TIMESTAMPdata type, which has nothing to do with dates and times and should be called
Bottom line: don’t use
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
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);
@dt AS [DATETIMEFROMPARTS],
DATALENGTH(@dt) AS [@dtBytes],
@dt2 AS [DATETIME2FROMPARTS],
DATALENGTH(@dt2) AS [@dt2Bytes];
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
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
- It returns true if we pass in a valid
TIMEparameter. Shouldn’t this be
- It returns false if we pass in a valid
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),
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
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.