Tag: t-sql

DATEDIFF() Returns Wrong Results in SQL Server? Read This.

Posted on by Ian

If you’re getting some really weird results when using the DATEDIFF() function in SQL Server, and you’re convinced the function contains a bug, don’t tear your hair out just yet. It’s probably not a bug.

There are scenarios where the results produced by this function can be pretty whacky. And if you don’t understand how the function actually works, the results will look completely wrong.

Hopefully this article can help clarify how the DATEDIFF() function is designed to work, and provide some example scenarios of where your results might not be as you’d expect.

Continue reading →

A Workaround for DATEDIFF() Ignoring SET DATEFIRST in SQL Server (T-SQL Example)

Posted on by Ian

An interesting thing about the DATEDIFF() function in SQL Server is that it ignores your SET DATEFIRST value.

However, this is not a bug. Microsoft’s documentation for DATEDIFF() clearly states the following:

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

In case you don’t know, SET DATEFIRST sets the first day of the week for your session. It’s a number from 1 through 7 (which corresponds to Monday through Sunday).

The initial value for SET DATEFIRST is implicitly set by the language setting (which you can set with the SET LANGUAGE statement). The actual value will depend on the language that is set. For example the default value for the us_english language is 7 (Sunday), whereas the default for the British language is 1 (Monday).

However, you can use a SET DATEFIRST statement to override this so that you can keep using the same language while using a different day for the first day of the week.

But as mentioned, the SET DATEFIRST value has no effect on the DATEDIFF() function. The DATEDIFF() function always assumes that Sunday is the first day of the week regardless of your SET DATEFIRST value.

This can cause some interesting problems when using DATEDIFF() if you don’t know how it works.

If you find yourself in this situation, hopefully the examples on this page can help.

Continue reading →

Convert ‘time’ to ‘datetime2’ in SQL Server (T-SQL Examples)

Posted on by Ian

This article contains examples of converting a time value to a datetime2 value in SQL Server.

When you convert a time value to datetime2, extra information is added to the value. This is because the datetime2 data type contains both date and time information. The time data type, on the other hand, only contains time information.

More specifically, the date is set to ‘1900-01-01’ (unless it happens to get rounded up to ‘1900-01-02’), the time component is copied, and according to the Microsoft documentation, the time zone offset is set to 00:00 (even though the datetime2 data type is not time zone aware and doesn’t preserve any time zone offset).

When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value is rounded up.

Continue reading →

Convert ‘time’ to ‘datetimeoffset’ in SQL Server (T-SQL Examples)

Posted on by Ian

This article contains examples of converting a time value to a datetimeoffset value in SQL Server using Transact-SQL.

When you convert a time value to datetimeoffset, the date is set to ‘1900-01-01’ and the time is copied. A time zone offset is added and set to +00:00.

Continue reading →

DATEDIFF() vs DATEDIFF_BIG() in SQL Server: What’s the Difference?

Posted on by Ian

If you’ve ever needed to find the difference between two dates in SQL Server, you might have used the DATEDIFF() function. This function returns the amount of time between two dates using a datepart specified by you. For example, you could use it to return the number of days between date 1 and date 2. You can also get it to return the number of minutes, seconds, months, years, etc.

The DATEDIFF_BIG() function works exactly the same way, but with one subtle difference: Its return data type.

So the difference between these two functions is the data type of their return value.

  • DATEDIFF() returns a signed integer (int)
  • DATEDIFF_BIG() returns a signed big integer (bigint)

Continue reading →

How to Return the Unix Timestamp in SQL Server (T-SQL)

Posted on by Ian

You might have noticed that SQL Server doesn’t have an equivalent of MySQL‘s UNIX_TIMESTAMP() function.

However, it’s not that difficult to return the Unix timestamp in SQL Server.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is simply the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC). Therefore, in SQL Server we can use a couple of T-SQL functions to return this.

Continue reading →

Examples of Converting ‘time’ to ‘datetime’ in SQL Server (T-SQL)

Posted on by Ian

This article contains examples of converting a time value to a datetime value in SQL Server.

When you convert a time value to datetime, extra information is added to the value. This is because the datetime data type contains both date and time information. The time data type, on the other hand, only contains time information. Therefore, date information is added to the value when you perform such a conversion. Specifically, the date is set to ‘1900-01-01’.

Continue reading →

Examples of Converting ‘date’ to ‘datetimeoffset’ in SQL Server (T-SQL)

Posted on by Ian

This article contains examples of converting a date value to a datetimeoffset value in SQL Server.

When you convert a date value to datetimeoffset, extra information is added to the value. This is because the datetimeoffset data type contains both date and time information, as well as the time offset information. In other words, the datetimeoffset data type defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. The date data type, on the other hand, only contains date information.

When we convert from date to datetimeoffset, the time (and time zone offset) is automatically added to the value. However, you can always change the value if required (including the time zone offset).

The datetimeoffset data type also allows you to specify the fractional seconds precision. If you don’t specify this, it uses a scale of 7. This means it will include 7 digits on the right side of the decimal point.

Continue reading →