In SQL Server, you can use the T-SQL FORMAT() function to format the date and/or time. Simply provide two arguments; the date/time and the format to use.
The format is supplied as a format string. A format string defines how the output should be formatted.
The FORMAT() function also accepts an optional “culture” argument, which allows you to specify a language/locale that the results should adhere to.
Basic Example
Here’s a basic example of using the FORMAT() function to format a date.
Raw Date
First, here’s what the raw date looks like. If we run the following statement:
SELECT GETDATE();
We get a result that looks something like this:
2018-05-03 02:36:54.480
Formatted Date
Now, we can use the FORMAT() function to format that date and time into our preferred format. For example, we could do this:
SELECT FORMAT( GETDATE(), 'D');
Which results in this:
Thursday, May 3, 2018
This is just one of many formats that we could choose. Here’s another:
SELECT FORMAT( GETDATE(), 'd');
Which results in this:
5/3/2018
The actual results will vary depending depending on the culture being used. By default, the language of the current session is used, but you can also override this with a third (“culture”) argument.
Setting a Locale
We could add a third (“culture”) argument to the above code to determine the locale to use for the date’s format.
So we could do this for example:
SELECT FORMAT( GETDATE(), 'd', 'en-gb');
Which results in this:
03/05/2018
Here’s another example:
SELECT FORMAT( GETDATE(), 'd', 'zh-cn');
Which results in this:
2018/5/3
If the culture argument is not provided, the language of the current session is used.
Here’s how to find the language of the current session, and here’s how to set it.
Note that the current language will usually be the same as the user’s default language, but this might not be the case if the user has changed the current language using SET LANGUAGE. In any case, you can also find out what the default language is.
As you might imagine, you could get quite different results depending on your current language or the value of any “culture” argument. See How Language Settings can Affect your FORMAT() Results for more examples.
Extracting the Month/Week/Year
You can use the FORMAT() function to return only the month part of the date, or the week, or year as required.
Note that these are case-sensitive. For example, MMM formats the month differently to mmm.
Month
Example code for returning the month part:
SELECT FORMAT( GETDATE(), 'MMM');
Result:
May
Year
Example code for returning the year part:
SELECT FORMAT( GETDATE(), 'yyyy');
Result:
2018
Day
Example code for returning the day part:
SELECT FORMAT( GETDATE(), 'dddd');
Result:
Thursday
Extracting the Time
You can also use the FORMAT() function to return the time in a specified format.
Here’s an example:
SELECT FORMAT( GETDATE(), 'hh.mm');
Result:
03.37
The AM/PM Designator
You can also add tt to include the AM/PM designator:
SELECT FORMAT( GETDATE(), 'hh.mm tt');
Result:
03.37 AM
Combining Format Strings
You can also combine these format strings to provide your own customized date format. Example:
SELECT FORMAT( GETDATE(), 'hh.mm tt dddd, dd MMMM, yyyy');
Result:
03.41 AM Thursday, 03 May, 2018
Date and Time Format Specifiers Reference
The following articles contain all the date and time format specifiers that can be used with the FORMAT() function in SQL Server, along with T-SQL examples:
These are the same date and time format specifiers that are supported by the .NET Framework (the FORMAT() function relies on the .NET Framework).
Numeric Format Specifiers Reference
The following articles contain all the numeric format specifiers you can use with the FORMAT() function (i.e. for formatting numbers):
These are the same numeric format specifiers that are supported by the .NET Framework.
Other Date Functions
T-SQL also includes a bunch of other functions that help you to extract parts from dates. These include functions such as DAY(), MONTH(), YEAR(), DATEPART(), and DATENAME().