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:
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
3 comments:
How to get day name (like Saturday/Sunday) from date format MM/DD/YYYY
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.
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
I WOULD BE DELIGHTED TO HEAR FROM YOU