Back to list SQL Server How to Change Date and Time Formats in T-SQL Database: SQL Server Operators:FORMAT() 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. IdFirstNameLastNameRegistrationDate 1JaneWilliams2019-06-20 2GabrielBrown2019-02-02 3LoraFolk2016-11-05 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: FirstNameLastNameFormattedRegistrationDate JaneWilliamsThursday, 20 June, 2019 GabrielBrownSaturday, 2 February, 2019 LoraFolkSaturday, 5 November, 2016 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: specifierdescription dDay in the range 1-31 ddDay in the range 01-31 dddThe abbreviated name of the day of the week ddddThe full name of the day of the week MMonth from 1 to 12 MMMonth from 01 to 12 MMMThe abbreviated name of the month MMMMThe full name of the month y2-digit year, from 0 to 99 yy2-digit year from 00 to 99 yyyy4-digit year gEra (e.g. A.D.) hHour from 1 to 12 (12-hour clock) hhHour from 01 to 12 (12-hour clock) HHour from 0 to 23 (24-hour clock) HHHour from 00 to 23 (24-hour clock) mMinute from 0 to 59 mmMinute from 00 to 59 sSecond from 0 to 59 ssSecond from 00 to 59 tFirst character of AM or PM (e.g. 9A, 5P) ttAM or PM zHours offset, without leading zero (e.g. +3) zzHours offset, with leading zero (e.g. +03) 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: FirstNameLastNameRegistrationDate JaneWilliamsDonnerstag, 20 Juni, 2019 GabrielBrownSamstag, 2 Februar, 2019 LoraFolkSamstag, 5 November, 2016 Recommended courses: SQL Basics in SQL Server Common Functions in SQL Server Recommended articles: 5 SQL Functions for Manipulating Strings 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Get the Current Date (Without Time) in T-SQL How to Get the Current Date and Time (Without Time Zone) in T-SQL Tags: SQL Server Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.