Tag: date format

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 →

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

Posted on by Ian

When using SQL Server, you have a few different options when you need to return the month name from a date using T-SQL. By month name, I’m not talking about the month number (such as 07). I’m talking about the full name of the month (such as July).

For example, when given a date of 2018-07-01, you want July to be returned.

This article presents three ways to return the month name from a date in SQL Server using T-SQL.

Continue reading →

How to Change the Current Date Format in SQL Server (T-SQL)

Posted on by Ian

When you connect to SQL Server, usually the date format is determined by your language. The default language for a session is the language for that session’s login, unless overridden on a per-session basis by using the Open Database Connectivity (ODBC) or OLE DB APIs.

The date format setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.

Despite the fact that the your session’s language determines the date format, you can override the date format if required. For example, if your language is us_english, the date format will be mdy (so that 07/01/2018 represents the 1st of July and not the 7th of January). You can change this so that the date format is dmy (or any other format) while the language remains us_english.

You can use T-SQL to explicitly set the date format of the current session by using the SET DATEFORMAT statement.

Continue reading →

How to Find the Date Format Being Used in the Current Session in SQL Server (T-SQL)

Posted on by Ian

When using SQL Server, your current connection includes a number of options that determine things like the language, date formats, etc. These could be set at whatever the default is, but they can also be overridden during the session by using a SET statement.

The date format affects the interpretation of character strings as they are converted to date values for storage in the database. When the language is set using SET LANGUAGE, the date format setting is implicitly set accordingly. This can be explicitly overridden with the SET DATEFORMAT statement.

In any case, you can find the current date format by using the DBCC USEROPTIONS command. This command returns the SET options that have been set for the current connection.

Continue reading →

How to Find the Date Formats Used for a Specific Language in SQL Server (T-SQL)

Posted on by Ian

When working with dates in SQL Server, it’s easy to get tripped up with different date formats. For example, someone from the US might consider 01/07/2018 to mean the 7th of January, but someone from the UK might consider it to mean the 1st of July.

In many cases you might not even know which date format is used for any particular language/culture. Fortunately, SQL Server stores this information in its resource database, and you can retrieve it by using either of the two methods below.

Continue reading →