Convert a Date to Another Time Zone in SQL Server

The AT TIME ZONE clause was introduced in SQL Server 2016 to convert a date into a datetimeoffset value in a target time zone.

This function is similar to some other T-SQL functions, such as SWITCHOFFSET() and TODATETIMEOFFSET(), however, the AT TIME ZONE clause allows/(requires) you to specify the time zone offset by name, instead of an actual offset value.

This article explores how AT TIME ZONE works, and explains its benefits when compared to the other functions mentioned.

Continue reading

Format sysjobhistory datetime & duration Columns in SQL Server

If you’ve ever queried the sysjobhistory table in the msdb database, you’ll probably know that the datetime and duration columns are stored as integers.

In particular, when you query this table, the run_date, run_time, and duration columns are returned as integers, which can make it difficult to read.

Below is a query you can use to return this data in an easier to read format.

Continue reading

Remember This When Formatting a TIME Data Type in SQL Server (T-SQL)

In SQL Server, when you use the T-SQL FORMAT() function to format a time data type, you need to remember to escape any colons or periods in your format string.

This is because the FORMAT() function relies upon CLR formatting rules, which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

Continue reading