Formatting SQL Server DateTime

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. For front end presentation it will be required to format the DateTime whatever circumstances. Kalen Delaney’s provide us a solution. Here i want to discuss the most common aspects of formatting Sql Server DateTime. Before using enlisted styles to format the DateTime why not we are trying to dvelop a custom method for common “dd/MM/yyyy” format.

The below sql will give you the most common format:


OUTPUT: 06/08/2014

In the above example DAY,MONTH,YEAR return you the day no, month no & year respectively. RIGHT method is used to keep the fixed 2 length string & RTRIM is used to merge the numeric value with string “/”.

It looks hard. We have a easy way which I want to share with you.

To do that 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

Ok now you know the style value and its corresponding format. 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 C# or VB.Net 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/09
SELECT CONVERT(VARCHAR, getdate(), 10) 04-25-09
SELECT CONVERT(VARCHAR, getdate(), 100) Apr 25 2009 1:25PM
SELECT CONVERT(VARCHAR, getdate(), 114) 13:25:48:153

Note: The above all formats may does not satisfy your condition. So you have to create a custom statement like my first example.

Posted in Database, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *