How to Change Date and Time Formats in T-SQL

Problem:

You’d like to change the format of a date field or value in a SQL Server database.

Example:

Our database has a table named Patient with data in the columns Id, FirstName, LastName, and RegistrationDate.

Let’s change the format of each patient’s registration date. We’ll put the name of the weekday first, followed by the month day and name and a 4-digit year (e.g. “Friday, 27 December 2019”).

Solution:

We’ll use the FORMAT() function to change the format of the date in the RegistrationDate column.

SELECT  
  FirstName,
  LastName,
  FORMAT(RegistrationDate ,'dddd, d MMMM, yyyy') AS FormattedRegistrationDate
FROM Patient;

Here’s the result of the query:

Discussion:

We can change how a given date is displayed with the FORMAT() function. This function takes two mandatory arguments and one optional argument. In our example, we used only the two mandatory arguments. The first is a date, which can be from a date/time/datetime column or any expression that returns a date or time. (In our example, we use the column RegistrationDate.) The second argument is a string containing the new date format. In our example, we used 'dddd, dd MMMM, yyyy':

• dddd – The name of the day of the week.
• d – The day of the month, from 1 to 31.
• MMMM – The full name of the month.
• yyyy – The four-digit year.

The table below presents more date/time format specifiers:

You can learn more in the Microsoft documentation for standard date and time formats and custom formats.

If you would like to format this date for a specific language and/or country, use the third optional argument: culture. This argument is a string containing the culture code for a particular region or country. (Culture codes are made up of a language code, a dash, and a country code.) In the example below, we use the culture code for Germany, which combines the German language code ('de') with the country code for Germany ('DE), i.e. 'de-DE'. The resulting date is displayed in German and formatted as a German audience would expect to see it.

SELECT  
  FirstName,
  LastName,
  FORMAT(RegistrationDate ,'dddd, d MMMM, yyyy', 'de-DE') AS FormattedRegistrationDate
FROM Patient;

Here’s the result of this query: