There are many similarities between
DATEPARTreturns the date or time part as an integer,
DATENAMEreturns the part as a character string.
DATENAMEfunction also takes two parameters: the date or time part we want back, and the input date. Just as we saw with
DATEPART, the documentation indicates the input date parameter must be an “expression that can resolve to one of the following data types: date, smalldatetime, datetime, datetime2, datetimeoffset, or time.”
Similarly, the date and time parts that can be returned look much like those in
DATEPART, which gives us another opportunity for the reminder that we should avoid using the available abbreviations in order to help with writing clearly understandable code.
DATENAME is a useful function for displaying parts of dates & times, but Randolph does lay out the caveats.
As we learned some time ago, an
INT(integer) in T-SQL uses four bytes, and has a maximum value greater than zero of over 2 billion (there are more than 4 billion values in an integer if we take the negative values into account).
Why then are date and time parts expressed as an
INT, which have a lot of overhead for values like 24, 31, and 60?
There are two reasons:
Integers make things simpler. When writing arithmetic in T-SQL and other programming languages, the default data type is usually an integer. Having to memorise which data types are returned from built-in functions becomes unnecessary when we know it will be an
INT. Yes, it uses extra memory, but four bytes is a reasonable trade-off against complexity.
One of the return values is nanoseconds. Although
DATETIME2(7)only has a granularity down to 100 nanoseconds,
DATEPARTallows us to return this value, which requires a data type large enough to contain an integer up to 1 billion (nanoseconds can range from 0 to 999,999,900 in increments of 100).
Randolph also explains what happened a few years back to cause iPhone alarms not to fire on January 1st and 2nd.
Today, we’ll compare 3 other methods to the DATEADD/DATEDIFF method:
- Taking advantage of the fact that a datetime datatype is stored as a float, with the decimal being fractions of a day and the whole numbers being days, we will convert the datetime to float, taking the floor (just the whole numbers), and converting back to datetime.
- Using the DATEADD/DATEDIFF routine.
- Converting the datetime to DATE and back to datetime.
- Converting the datetime to varbinary (which returns just the time), and subtracting that from the datetime value.
While there are other ways of stripping the time (DATETIMEFROMPARTS, string manipulation), those ways are already known as poorly performing. Let’s just concentrate on these four.
Click through for the methods, as well as a performance test to see which is fastest.
CURRENT_TIMESTAMPis 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
At one point I used
GETDATE() with the thought of portability in mind. Since then, my thoughts on code portability have changed and regardless, as Randolph mentions, it’s better to use
DATETIME2 functions to avoid precision issues with
It occurred to me that we haven’t covered the
TIMESTAMPdata type in this series about dates and times.
TIMESTAMPis the Windows Millennium Edition of data types. It has nothing to do with date and time. It’s a row version. Microsoft asks that we stop calling it
DECIMALis a synonym of
NUMERIC, so too is
TIMESTAMPa synonym of
ROWVERSION. Please call it a
ROWVERSIONand pretend that
TIMESTAMPdoesn’t exist. Microsoft is deeply sorry for the confusion.
As I say, dates and times are hard. But at least this is easy: if you don’t use it, you won’t have problems with it.
What do we mean by local date and time?
As discussed previously, SQL Server is not time zone aware, nor does it have to be. This is because the operating system that SQL Server runs on can have multiple custom regional settings depending on which user is logged into the server.
This holds true for the SQL Server service account as well, which is just another user on the operating system. When any of these functions is called, it is asking for the date and time from the operating system.
If you’re going to use DATETIME2 (which you generally should), take advantage of the precision that
SYSUTCDATETIME() gives you over
I’m currently working on a SQL code migration from Firebird to SQL Server and I hit an error that I haven’t seen for some time.
The error message is the following:
Msg 206, Level 16, State 2, Line 4
Operand type clash: datetime2 is incompatible with int
This ringed the bell right away! Somewhere on the code someone was trying to do an arithmetic calculation without using the proper function.
Read on for the solution.
Here we see a Seek Predicate that looks for OrderDate values between two values that have been worked out elsewhere in the plan, but creating a range in which the right values must exist. This isn’t >= 20110805 00:00 and < 20110806 00:00 (which is what I would’ve made it), it’s something else. The value for start of this range must be smaller than 20110805 00:00, because it’s >, not >=. All we can really say is that when someone within Microsoft implemented how the QO should respond to this kind of predicate, they gave it enough information to come up with what I call a “helper predicate.”
Now, I would love Microsoft to make more functions sargable, but that particular request was Closed long before they retired Connect.
But maybe what I mean is for them to make more helper predicates.
The problem with helper predicates is that they almost certainly read more rows than you want. But it’s still way better than looking through the whole index.
Read the whole thing.
DATETIMEOFFSETworks the same way as the
DATETIME2data type, except that it is also time zone aware. It is formatted as
Got all that?
YYYYrepresents a four-digit year,
MMis a two-digit month between 1 and 12,
DDis a two-digit day between 1 and 31 depending on the month,
HHrepresents a two-digit hour between 0 and 23,
mmis the minutes between 0 and 59, while
ssis the number of seconds between 0 and 59. Once again,
nrepresents between zero and seven decimal places in a fraction of a second.
The main difference from
DATETIME2is the time zone offset at the end, which is the number of hours and minutes as an offset from UTC time.
Read on for more. I generally don’t use this date type much, preferring to stick with
DATETIME2 and saving data as UTC.
Everything is fine … except if I share my code with someone from another time zone. The function
DateTimeZone.ToLocalis relying on regional settings and in that case my conversion should always be from UTC to “Brussels time”.
I didn’t find any other way to ensure that I’m always converting from UTC to “Brussels time” than implementing the conversion by myself. That’s the goal of the following function
Looks like there may not be a nice “convert to a different time zone” here like
lubridate::with_tz() does in R.