Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

How To Format DATETIME Values to Strings with the CONVERT() Function?

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

(Continued on next topic...)

1. How To Add or Remove Days on Date and Time Values?
2. Can Date and Time Values Be Converted into Integers?
3. Can Integers Be Converted into Date and Time Values?
4. Are DATETIME and NUMERIC Values Convertible?
5. Can a DATETIME Value Be Subtracted from Another DATETIME Value?
6. What Are the Date and Time Functions Supported by SQL Server 2005?
7. How To Increment or Decrement Parts of DATETIME Values?
8. How To Use DATEADD() Function?
9. How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
10. How To Calculate Age in Days, Hours and Minutes?
11. How To Get Month and Weekday Names from DATETIME Values?
12. How To Get Parts of DATETIME Values as Integers?
13. How To Get Year, Month and Day Out of DATETIME Values?
14. What Is the Difference Between GETDATE() and GETUTCDATE()?
15. How To Format Time Zone in +/-hh:mm Format?
16. How To Format DATETIME Values to Strings with the CONVERT() Function?
17. How To Truncate DATETIME Values to Dates without Time?
18. How To Set Different Parts of a DATETIME Value?