Thursday, May 27, 2010

SQL Date and DateTime Formats

A while ago (ok, 3 years ago) i posted a script to display all sql date formats.

The output of that script is below for quick reference (as I keep forgetting the codes)

To display a DATETIME in the desired format, use -

CONVERT(VARCHAR(30),GETDATE(), formatcode)

e.g. SELECT CONVERT(VARCHAR(30),GETDATE(),21)
which provides 2010-05-28 11:09:34.263


Format codes come from the table below. You can reproduce this results set by running this script

formatcodedatestring
105/28/10
210.05.28
328/05/10
428.05.10
528-05-10
628 May 10
7May 28, 10
810:34:48
9May 28 2010 10:34:48:687AM
1005-28-10
1110/05/28
12100528
1328 May 2010 10:34:48:687
1410:34:48:687
202010-05-28 10:34:48
212010-05-28 10:34:48.693
2205/28/10 10:34:48 AM
232010-05-28
2410:34:48
252010-05-28 10:34:48.693
100May 28 2010 10:34AM
10105/28/2010
1022010.05.28
10328/05/2010
10428.05.2010
10528-05-2010
10628 May 2010
107May 28, 2010
10810:34:48
109May 28 2010 10:34:48:703AM
11005-28-2010
1112010/05/28
11220100528
11328 May 2010 10:34:48:703
11410:34:48:703
1202010-05-28 10:34:48
1212010-05-28 10:34:48.703
1262010-05-28T10:34:48.703
1272010-05-28T10:34:48.703
13015 ????? ??????? 1431 10:34:48
13115/06/1431 10:34:48:703AM

Link : How to format datetime in SQL 2005

No comments: