sql server 2000 + How to display date time

Over on Experts Exchange I saw a question that I get all the time, “How do I format this DATETIME like x?”  If they’re asking to learn, I point them over to the CAST and CONVERT article on msdn.  If you wish to change a Microsoft SQL DATETIME column from the standard format 2008-01-28 12:43:13.210 to any other format you need to use CONVERT.  CONVERT will let you choose any option from the following table as your formatting option.

Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
0 or 100 (1,2) Default mon dd yyyy hh:miAM (or PM)
1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yyyy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106(1) dd mon yy
7 107(1) Mon dd, yy
8 108 hh:mi:ss
9 or 109 (1,2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd


13 or 113 (1,2) Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
14 114 hh:mi:ss:mmm(24h)
20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127(6, 7) ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ

(no spaces)

130 (1,2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM

In order to use the formatting, simply use CONVERT(VARCHAR(length), dateColumn, formatOption).  I’ve included a few examples below.

declare @ExampleDateTime datetime
set @ExampleDateTime = '2008-01-28 12:43:13.210'

SELECT  CONVERT(varchar(10), @exampleDateTime, 101) AS Result

SELECT  CONVERT(varchar(26), @exampleDateTime, 9) AS Result
Jan 28 2008 12:43:13:210PM

SELECT  CONVERT(varchar(20), @exampleDateTime) AS Result
Jan 28 2008 12:43PM

While there was quite a few people offering assistance on how to use the CONVERT function I hope you will add a bookmark to the Microsoft Books Online article I’ve referenced.  It will save you a ton of time when you have to format your dates a certain way.  If you have to write reports, CONVERT will become a common site in your T-SQL code.

If you have any questions about how to accomplish your goals in SQL, please send on your questions.  I’m here to help!


Leave a Reply

Your email address will not be published. Required fields are marked *