Tag: date format

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 →

What is a Format String in SQL Server?

Posted on by Ian

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

Continue reading →

Custom Date/Time Format Strings Supported by FORMAT() in SQL Server

Posted on by Ian

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.

Continue reading →

Standard Date/Time Format Strings Supported by FORMAT() in SQL Server

Posted on by Ian

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.

Continue reading →

How to Specify the Invariant Culture when using FORMAT() in SQL Server

Posted on by Ian

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

Continue reading →

How to Display a Date in German Format in SQL Server (T-SQL)

Posted on by Ian

When formatting a date using the FORMAT() function in SQL Server, the date will be formatted according to the language of your local session. However, you can override this by specifying a culture to use, or using a custom date format.

This article demonstrates how to explicitly specify a German date format by using the optional “culture” argument of the FORMAT() function. It also demonstrates how to use your own custom date format if that is more desirable.

Continue reading →

How to Display a Date in British Format in SQL Server (T-SQL)

Posted on by Ian

This article demonstrates how to explicitly format a date in Great Britain English format when using the T-SQL FORMAT() function in SQL Server.

You may or may not need to use this argument, depending on the language of your local session. However, here’s how to explicitly specify Great Britain English date format.

Continue reading →

How to Display a Date in US Date Format in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, you can use the T-SQL FORMAT() function to display a date in the desired format. This function accepts an optional “culture” argument, which you can use to specify US date format.

You may or may not need to use this argument, depending on the language of your local session. However, here’s how to explicitly specify US date format.

Continue reading →

3 Ways to Get the Day Name from a Date in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, just as you can use T-SQL to get the month name from a date, you can also use the same methods to get the day name. By day name, I mean Monday or Tuesday for example, and not the date number or number of the day of the week (which you can also get if you need it).

Here are three ways to return the day name from a date in SQL Server using T-SQL.

Continue reading →