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

formatcode	datestring
1	05/28/10
2	10.05.28
3	28/05/10
4	28.05.10
5	28-05-10
6	28 May 10
7	May 28, 10
8	10:34:48
9	May 28 2010 10:34:48:687AM
10	05-28-10
11	10/05/28
12	100528
13	28 May 2010 10:34:48:687
14	10:34:48:687
20	2010-05-28 10:34:48
21	2010-05-28 10:34:48.693
22	05/28/10 10:34:48 AM
23	2010-05-28
24	10:34:48
25	2010-05-28 10:34:48.693
100	May 28 2010 10:34AM
101	05/28/2010
102	2010.05.28
103	28/05/2010
104	28.05.2010
105	28-05-2010
106	28 May 2010
107	May 28, 2010
108	10:34:48
109	May 28 2010 10:34:48:703AM
110	05-28-2010
111	2010/05/28
112	20100528
113	28 May 2010 10:34:48:703
114	10:34:48:703
120	2010-05-28 10:34:48
121	2010-05-28 10:34:48.703
126	2010-05-28T10:34:48.703
127	2010-05-28T10:34:48.703
130	15 ????? ??????? 1431 10:34:48
131	15/06/1431 10:34:48:703AM

Link : How to format datetime in SQL 2005