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:

datetime2(3) uses fewer bytes and is more accurate than datetime

Click to embiggen

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.

10 thoughts on “Dates and Times in SQL Server: more functions you should never use

  • Hi Randolph West,

    Thanks for providing details on Datetime Functions.

    To just add one more point on “Current_Timestamp” function, Even though if we give “Current_Timestamp” as default constraint/value to any column Table will consider GETDATE() as the Default Value.

    Thanks,
    Narendra

  • Good article – but it would have been nice to include a valid substitute for IsDate(), or just state that there isn’t one.

  • Heh… if you think the DATETIME datatype is “old and bad”, it might be because you don’t actually know how easy it is to do some really complex things with and it might also be because your not familiar with ANSI Standards, which say things like Period (duration)=EndDatetime-StartDateTime and EndDateTime=StartDateTime+Period. It turns out that the “old and bad” DATETIME datatype is ANSI compliant because it can actually do such things with little fanfare whereas the relatively “new” datatypes can’t do such things and are, therefor, NOT ANSI compliant.

    Before you claim that the DATETIME datatype is “old and bad”, learn just how much better it can be than the “new” stuff.

  • I can’t see any objection to not having brackets in the CURRENT_TIMESTAMP. This is in ANSI/ISO standard, so we should applaud any attempt on the part of Microsoft to conform to standards. When we created this we didn’t see it as a function, but rather a “system level constant”; actually, standard SQL has some weird syntax in that we do have brackets, we often embed keywords inside the parameter list. CAST( AS ) is just one example. I’ll be honest, I have no idea why we did this when I was on the ANSI X3H2 committee, but it does make SQL look different from other languages.

    The old Sybase getdate() function is pure C and UNIX programming. It doesn’t even look like in SQL word!

    The DATETIME2FROMPARTS() function is a bit weird, but probably one of the acceptable localizations, since Microsoft doesn’t support the standard TIMESTAMP data type. I’m trying to figure out why anybody would want to assemble temporal data from nontemporal parts in a correctly designed schema.

    I strongly agree with you about the ISDATE() function. By the time data gets into the database, other layers of the tiered architecture should have put it in the one standard format allowed by your SQL engine. But even more than that, the highly proprietary bit data type is now a numeric; that means it can be null!.

  • Jeff Moden & Joe Celko – thank you for your responses. I actually tried switching to datetimestamp2 a few years back based on one of Joe’s articles. It works great BUT does not play well with others in my experience. Anytime I had to interact with other date types, push through SSIS, work with older versions of SQL, etc… I got errors and/or an automatic data type change to string 🙁 It was stinky. Rightly or wrongly, in lieu of anything better I have continued using current_timestamp.

  • I guess it depends on what you’re trying to achieve. Yes, you can obtain higher precision with DATETIME2, however, that may not be what you’re seeking and possibly every 3ms is “good enough”. Possibly UTC isn’t what’s desired. I worked on a trading app and we purposely set the server time to Easter time so it would track with the market. I’d say, know your the purpose before making the change. After all, we’re not supposed to be Lemmings. Jeff Moden brings up some of the (ANSI compliant) uses of “date math” you can do with the old DATETIME data type.

    • Thanks for stopping by. Unfortunately your methodology is flawed.

      I addressed this weirdness in a post from last year (which you can find here: https://bornsql.ca/blog/datetime2-8-bytes-binary-9-bytes/). What’s happening is you’re casting the data type to a binary value, which has to append an extra byte in the conversion to ensure no information is lost.

      I did a little investigation using the same method I used in a previous post (you can see that here: https://bornsql.ca/blog/bit-columns/), and I can confirm that the length is definitely 8 bytes for a DATETIME2(7) in the data page, not 9 bytes as you suggest.

      Here is output from DBCC PAGE:

      This is expected because the column definition itself contains the data type and precision (in sys.columns), so there’s no need to include the precision and scale in every row. It’s only when converting the data to binary that it has to add the extra byte.

      This means that DATETIME2(3) is 7 bytes as I stated.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: