Author: Ian

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

Posted on by Ian

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

When you convert a datetime value to datetimeoffset, the resulting value will depend on the fractional seconds precision that you assign to datetimeoffset, as well as any time zone offset you specify.

The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

The datetimeoffset data type, on the other hand, allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default). It also has a time zone offset and can preserve any offsets in the original value. However, datetime has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset defaults to +00:00.

SQL Server actually has the TODATETIMEOFFSET() function, which is specifically designed to convert a date/time value to datetimeoffset and add a time zone offset. However, there’s a subtle detail to be aware of when using this function, and I explain this below (with examples).

Continue reading →

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

Posted on by Ian

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

When you convert a datetime value to datetime2, the resulting value will depend on the fractional seconds precision that you assign to datetime2.

The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

The datetime2 data type, on the other hand, allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default).

Continue reading →

How to Return a List of Data Types in SQL Server (T-SQL)

Posted on by Ian

If you ever need to get a list of data types in SQL Server, you can use one of the system views to do just that.

In particular, you can use the sys.types system catalog view. This view returns all system-supplied and user-defined data types defined in the database. If you’re using SQL Server 2000 sys.systypes should do the trick.

Continue reading →

How Language Settings can Affect your FORMAT() Results in SQL Server (T-SQL Examples)

Posted on by Ian

It can be easy to forget that the T-SQL FORMAT() function provides locale-aware formatting. Locale-aware means that the locale can affect the results. In other words, the exact output you get will depend on the locale.

By default, the function uses the language of the current session to determine the locale. However, this can be overridden by passing a “culture” argument to the function. Doing this allows you to provide results for a particular locale without having to change the language of the current session.

This article contains examples of how locale can affect the results when using the FORMAT() function in SQL Server.

Continue reading →

How to Format Negative Values with Brackets in SQL Server (T-SQL)

Posted on by Ian

Here’s a quick way to add brackets around negative numbers in SQL Server when using the FORMAT() function.

The goal here is that brackets are only added to negative values. No brackets are added to positive values or zeros. Also, the brackets replace any minus sign that would otherwise be displayed (in other words, no minus sign is displayed when the brackets are used).

Although formatting is often best left to the presentation layer, there may be cases that dictate a T-SQL solution in SQL Server. In such cases, hopefully this article helps.

Continue reading →

Standard Numeric Format Strings Supported by FORMAT() in SQL Server

Posted on by Ian

This article provides a reference for the standard numeric format specifiers that can be used when formatting numbers using the FORMAT() function in SQL Server. Examples included.

Some of the examples use precision specifiers (these consist of one or two digits appended to the format specifier). Precision specifiers can be a value from 0 to 99, which specifies the precision of the result. How it works depends on the format specifier being used. For some format specifiers, it will specify the total number of digits in the result, for others it will specify the number of decimal places. In other cases it will be ignored altogether.

Continue reading →

How to Apply Conditional Formatting to a Number in SQL Server using FORMAT()

Posted on by Ian

Perhaps one of the lesser-known features of the FORMAT() function in SQL Server is one that enables you to apply conditional formatting to a number.

It’s more of a .NET feature than it is a SQL Server (or T-SQL) feature, but SQL Server/T-SQL supports it all the same, allowing you to take full advantage of the ability to apply conditional formatting to numbers.

It all comes down to the format string you pass to the FORMAT() function.

You can pass a format string that specifies how the number should be formatted, depending on whether it’s positive, negative, or zero.

Continue reading →