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)Defaultmon dd yyyy hh:miAM (or PM)
1101U.S.mm/dd/yyyy
2102ANSIyy.mm.dd
3103British/Frenchdd/mm/yyyy
4104Germandd.mm.yy
5105Italiandd-mm-yy
6106(1)-dd mon yy
7107(1)-Mon dd, yy
8108-hh:mi:ss
-9 or 109 (1,2)Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USAmm-dd-yy
11111JAPANyy/mm/dd
12112ISOyymmdd

yyyymmdd

-13 or 113 (1,2)Europe default + millisecondsdd mon yyyy hh:mi:ss:mmm(24h)
14114-hh:mi:ss:mmm(24h)
-20 or 120 (2)ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)
-21 or 121 (2)ODBC canonical (with milliseconds)yyyy-mm-dd hh:mi:ss.mmm(24h)
-126 (4)ISO8601yyyy-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
Result
------
01/28/2008
SELECT  CONVERT(varchar(26), @exampleDateTime, 9) AS Result
Result
--------------------------
Jan 28 2008 12:43:13:210PM
SELECT  CONVERT(varchar(20), @exampleDateTime) AS Result
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!

References

Tags: ,

No comments yet.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.