Friday, April 24, 2009

SQL Server Date Time Format




Please visit my new Web Site WWW.Codedisplay.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:

StyleFormatExample
1MM/dd/yy04/25/09
2yy.MM.dd09.04.25
3dd/MM/yy25/04/09
4dd.MM.yy25.04.09
5dd-MM-yy25-04-09
6dd MMM yy25 Apr 09
7MMM dd, yyApr 25, 09
10MM-dd-yy04-25-09
11yy/MM/dd09/04/25
12yyMMdd090425
100 or 0MMM dd yyyy hh:miAM (or PM)Apr 25 2009 1:10PM
101MM/dd/yyyy04/25/2009
102yyyy.MM.dd2009.04.25
103dd/MM/yyyy25/04/2009
104dd.MM.yyyy25.04.2009
105dd-MM-yyyy25-04-2009
106dd MMM yyyy25 Apr 2009
107MMM dd, yyyyApr 25, 2009
108hh:mm:ss13:12:22
109 or 9MMM dd yyyy hh:mi:ss:mmmAM (or PM)Apr 25 2009 1:12:40:263PM
110MM-dd-yyyy04-25-2009
111yyyy/MM/dd2009/04/25
112yyyyMMdd20090425
113 or 13dd MMM yyyy hh:mm:ss:mmm(24h)25 Apr 2009 13:13:30:983
114hh: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.

QueryOutput
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

3 comments:

Kartheek ch said...

How to get day name (like Saturday/Sunday) from date format MM/DD/YYYY

Shawpnendu said...

HI Kartheek,
To get the date name please read my another post:

http://shawpnendu.blogspot.com/2009/05/datepart-dateadd-datediff-sql-server.html

Let me know if your problem does not resolved.

Memtech said...

This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.

http://mindstick.com/Articles/b08c8553-60a2-4f35-a58f-60c538c7d136/?%E2%80%98SELECT%E2%80%99%20Command%20with%20Date%20Method

Its also helped me lot to complete my task.
Thanks

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash