SQL Server DateTime Format

In this post I am going to explain about different DateTime formats in sqlserver. Most of the developers expect that sql server date time format will behave like VB or .net but the reality is different. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. This means that it is no longer a DateTime. It is a VARCHAR.

To do this  we will use CONVERT function in the following way:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Where data_type will be varchar or char, length will be the total length of your expected format, expression will be any valid sql server expression & style will denote the output format.

The list of available styles are given below:

Style	Format	Example
1	MM/dd/yy	04/25/09
2	yy.MM.dd	09.04.25
3	dd/MM/yy	25/04/09
4	dd.MM.yy	25.04.09
5	dd-MM-yy	25-04-09
6	dd MMM yy	25 Apr 09
7	MMM dd, yy	Apr 25, 09
10	MM-dd-yy	04-25-09
11	yy/MM/dd	09/04/25
12	yyMMdd	090425
100 or 0	MMM dd yyyy hh:miAM (or PM)	Apr 25 2009 1:10PM
101	MM/dd/yyyy	04/25/2009
102	yyyy.MM.dd	2009.04.25
103	dd/MM/yyyy	25/04/2009
104	dd.MM.yyyy	25.04.2009
105	dd-MM-yyyy	25-04-2009
106	dd MMM yyyy	25 Apr 2009
107	MMM dd, yyyy	Apr 25, 2009
108	hh:mm:ss	13:12:22
109 or 9	MMM dd yyyy hh:mi:ss:mmmAM (or PM)	Apr 25 2009 1:12:40:263PM
110	MM-dd-yyyy	04-25-2009
111	yyyy/MM/dd	2009/04/25
112	yyyyMMdd	20090425
113 or 13	dd MMM yyyy hh:mm:ss:mmm(24h)	25 Apr 2009 13:13:30:983
114	hh:mi:ss:mmm(24h)	13:13:42:200

 

Now i want to show you how we can use this style or format in sql server query statement. You can also use this format from asp.net C# application when you built a query string. You can also use those formats in your where clause as conditional purposes.

Query	Output
SELECT CONVERT(VARCHAR, getdate(), 1)	04/25/12
SELECT CONVERT(VARCHAR, getdate(), 10)	04-25-12
SELECT CONVERT(VARCHAR, getdate(), 100)	Apr 25 2012 1:25PM
SELECT CONVERT(VARCHAR, getdate(), 114)	13:25:48:153