Category: Dates and Numbers

Randolph West shares some thoughts on the DATEPART function:

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:

1. 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.

2. One of the return values is nanoseconds. Although DATETIME2(7) only has a granularity down to 100 nanoseconds, DATEPART allows 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.

Wayne Sheffield compares the performance of four methods for removing time from a DateTime data type:

Today, we’ll compare 3 other methods to the DATEADD/DATEDIFF method:

1. 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.
2. Using the DATEADD/DATEDIFF routine.
3. Converting the datetime to DATE and back to datetime.
4. 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.

Randolph West shows a few functions which can retrieve current date and time information:

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 GETUTCDATE().

Claudio Silva points out a difference between the DATETIME and DATETIME2 data types:

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.