CONVERT() - Formatting DATETIME Values to Strings in SQL Server


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   
103    British/French   dd/mm/yyyy
104    German 
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:

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);
You were born on May 19, 1987, at 16:10:41


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

⇒⇒SQL Server Transact-SQL Tutorials

2017-02-08, 381👍, 0💬