How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL?

SQL Server 2005 offers no functions to format DATETIME values in your own format patterns. But it does provide you a number of pre-defined format patterns that you can use with the CONVERT(char_type, date, pattern_code) function. Commonly used pattern codes are:

Code   Name             Format
100    Default          mon dd yyyy hh:miAM/PM
101    U.S.             mm/dd/yyyy
102    ANSI             yyyy.mm.dd
103    British/French   dd/mm/yyyy
104    German           dd.mm.yy
105    Italian          dd-mm-yy
107                     Mon dd, yyyy
108                     hh:mi:ss
110    USA              mm-dd-yy
111    Japan            yy/mm/dd
121    ODBC canonical   yyyy-mm-dd hh:mi:ss.mmm

For examples, see the tutorial exercise below:

DECLARE @birth_date DATETIME;
SET @birth_date = '1987-05-19 16:10:41.403';
SELECT 'You were born on '
   + CONVERT(VARCHAR(40),@birth_date,107)
   + ', at '
   + CONVERT(VARCHAR(40),@birth_date,108);
GO
You were born on May 19, 1987, at 16:10:41

⇒ Truncating DATETIME Values to Dates without Time in SQL Server

⇐ Formatting Time Zone in +/-hh:mm Format in SQL Server

⇑ Date/Time Operations and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials