>SQL Server: 20 Most Commonly Used DateTime Formats


>

Dealing with datetime data type and conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.
Note: Use Convert function as follow
SELECT CONVERT(VARCHAR(20), GETDATE(), 108)
Format No
Output
101
U.S. mm/dd/yyyy
102
ANSI yy.mm.dd
103
British/French dd/mm/yyyy
104
German dd.mm.yy
105
Italian dd-mm-yy
106
dd mon yy
107
Mon dd, yy
108
hh:mi:ss
109
mon dd yyyy hh:mi:ss:mmmAM
110
USA mm-dd-yy
111
JAPAN yy/mm/dd
112
Yymmdd
113
dd mon yyyy hh:mi:ss:mmm(24h)
114
hh:mi:ss:mmm(24h)
120
yyyy-mm-dd hh:mi:ss(24h)
121
yyyy-mm-dd hh:mi:ss.mmm(24h)
126
yyyy-mm-ddThh:mi:ss.mmm
127
yyyy-mm-ddThh:mi:ss.mmmZ (With Time Zone)
130
dd mon yyyy hh:mi:ss:mmmAM (Hijri)
131
dd/mm/yy hh:mi:ss:mmmAM (Hijri)
Advertisements

Posted on February 8, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: