This article provides a reference for the custom numeric format specifiers that can be used when formatting numbers using the FORMAT() function in SQL Server. Examples included.
Tag: functions
How to Apply Conditional Formatting to a Number in SQL Server using FORMAT()
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.
What is a Format String in SQL Server?
In SQL Server, the FORMAT() function enables you to format date/time and number values as a formatted string by passing in a “format string” as the second argument (the first argument is the value that’s being formatted).
Here’s an example of this function in action:
FORMAT(@date, 'dd/MM/yyyy');
In this case the format string is dd/MM/yyyy.
This particular format string specifies that the @date value should be formatted with a two-digit day, two-digit month, and a four-digit year, in that order, and with forward slashes as the separators.
This would result in something like this:
21/05/2019
Custom Date/Time Format Strings Supported by FORMAT() in SQL Server
This article provides a reference for the custom date and time format specifiers that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples.
You can combine any of these format specifiers to produce a customized format string. See below for a code example and an explanation on how custom format strings work.
Standard Date/Time Format Strings Supported by FORMAT() in SQL Server
This article provides a list of the standard date and time format strings that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples using different cultures and data types.
These are the standard date and time format specifiers that are supported by the .NET Framework, and therefore, SQL Server. Each standard date and time format string is an alias for a custom date and time format string.
How to Specify the Invariant Culture when using FORMAT() in SQL Server
In SQL Server, you can use the FORMAT() function to format date/time and number values as strings. In particular, the function provides “locale-aware” formatting, and the function accepts a “culture” argument, which allows you to specify a culture or language to use for the actual format. For example, you can pass en-us to ensure the results are formatted in US English format.
The culture argument is optional, so if you don’t provide it, the output will be determined by the language of the current session.
The FORMAT() function accepts any culture supported by the .NET Framework as an argument (you’re not limited to the languages explicitly supported by SQL Server).
One of the cultures supported by the .NET Framework is the invariant culture. The invariant culture is culture-insensitive. More specifically, this culture is associated with the English language but not with any country/region.
To specify that FORMAT() should output the results using the invariant culture, simply use "iv" for the culture argument (the third argument).
DATEDIFF() Returns Wrong Results in SQL Server? Read This.
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.
A Workaround for DATEDIFF() Ignoring SET DATEFIRST in SQL Server (T-SQL Example)
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 DATEFIRSThas no effect onDATEDIFF.DATEDIFFalways 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 readingConvert ‘time’ to ‘datetime2’ in SQL Server (T-SQL Examples)
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.
Convert ‘time’ to ‘datetimeoffset’ in SQL Server (T-SQL Examples)
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.