SQL Server Date Time Format

Please visit my new Web Site https://coderstechzone.com

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 try to dvelop a custom method for common "dd/MM/yyyy" format.

For DATETIME built in function you can visit this link.

The below sql will give you the most common format:

SELECT RIGHT('0' + RTRIM(DAY(GETDATE())),2) + '/' + RIGHT('0' + RTRIM(MONTH(GETDATE())),2) + '/' + RTRIM(YEAR(GETDATE()))

FORMAT: 25/04/2009

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 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/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

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

To read more:
http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1/Working-with-SQL-Server-DateTime-Variables-Part-Two---Displaying-Dates-and-Times-in-Different-Formats.htm