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.
Photo by MIOPS Trigger on Unsplash.
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.
Great feedback Narendra. Thank you.
Good article – but it would have been nice to include a valid substitute for IsDate(), or just state that there isn’t one.
If on SQL 2012+ try this:
TRY_CONVERT(DATETIME2, ‘This is not a date’) IS NOT NULL
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.
I object to the DATALENGTH comparison.
The datalength function returns the much needed bytes after the precision is determined.
See http://www.sqltopia.com/internal-structures/datetime2/ how a datetime2(3) is really stored on a page.
Thanks for stopping by. Unfortunately your methodology is flawed.
I addressed this weirdness in a post from last year (which you can find here). 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), 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
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.
Ah, careful now, Randolph. swePeso is actually correct about DATETIME2(7) actually IS 9 bytes long. We don’t need any code to prove it, though. See the following Microsoft Documentation…
If you look at the following table included in that document…
Storage size 6 bytes for precision less than 3.
7 bytes for precision 3 or 4.
All other precision require 8 bytes.1 <–This "1" in the original document is superscripted, meaning to look at note "1".
…and then we look at note 1, note 1 says…
"1. The first byte of a datetime2 value stores the precision of the value, which means the actual storage required for a datetime2 value is the storage size indicated in the table above plus 1 additional byte to store the precision. This makes the maximum size of a datetime2 value 9 bytes – 1 byte stores precision plus 8 bytes for data storage at maximum precision."
p.s. That also means that the storage requirements for DATETIME2(3) is actually 8 bytes… 1 byte to store the precision and 7 bytes to store the date/time.
Honestly I’m surprised the precision isn’t stored in the column definition. Either way, 1ms vs 3ms for the same amount of space is a win for me.
Understood and totally agreed on that IF that was my only concern. Like I said before, “It Depends” on what you need to do with dates and times. Even if I only needed time accurate to the second or maybe even the minute, DATETIME has a whole lot more functionality than DATETIME2().
Anyway, thanks for the posts, Randolph. I appreciate what you do for this community.
Thank you, Jeff. That means a lot coming from you.
I am surprised MS added the note to the article.
I still think it’s incorrect, so I’ve written to some folks to find out what’s going on.
Microsoft has updated the documentation to remove that note because it was incorrect. For uncompressed rowstore values, DATETIME2(3) (for example) will use 7 bytes. For batch mode, the data type is normalized to 8 bytes in memory, regardless of precision. When converting to VARBINARY, the precision is prefixed to the value, meaning it can be as large as 9 bytes. Hopefully this is the final word on this topic.
Hi @Randolph, great article as always. BTW, talking about precision, have you noticed that SYSDATETIMEOFFSET loses 5 digits of precision in Azure SQL?
Thanks for sharing this, Francesco.