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!


By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

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