Sometimes your date and time format doesn’t come out well formatted as client wants to view in their reports. You have two choices either you have do at application level or database level. SQL SERVER provides various in built options to handle date and time format.
Here I am writing some of them using GETDATE () function, which gives the current date. Today is 6th March, 2012.
DATE FORMATS
| ||
Format No.
|
Query (Today’s date: 03/06/2012)
|
Output
|
1
|
SELECT CONVERT(VARCHAR, GETDATE(), 1)
|
03/06/12
|
2
|
SELECT CONVERT(VARCHAR, GETDATE(), 2)
|
12.03.06
|
3
|
SELECT CONVERT(VARCHAR, GETDATE(), 3)
|
06/03/12
|
4
|
SELECT CONVERT(VARCHAR, GETDATE(), 4)
|
06.03.12
|
5
|
SELECT CONVERT(VARCHAR, GETDATE(), 5)
|
06-03-12
|
6
|
SELECT CONVERT(VARCHAR, GETDATE(), 6)
|
06 Mar 12
|
7
|
SELECT CONVERT(VARCHAR, GETDATE(), 7)
|
Mar 06, 12
|
8
|
SELECT CONVERT(VARCHAR, GETDATE(), 8)
|
11:03:19
|
9
|
SELECT CONVERT(VARCHAR, GETDATE(), 9)
|
Mar 6 2012 11:04:45:850AM
|
10
|
SELECT CONVERT(VARCHAR, GETDATE(), 10)
|
03-06-12
|
11
|
SELECT CONVERT(VARCHAR, GETDATE(), 11)
|
12/03/06
|
101
|
SELECT CONVERT(VARCHAR, GETDATE(), 101)
|
03/06/2012
|
102
|
SELECT CONVERT(VARCHAR, GETDATE(), 102)
|
2012.03.06
|
103
|
SELECT CONVERT(VARCHAR, GETDATE(), 103)
|
06/03/2012
|
104
|
SELECT CONVERT(VARCHAR, GETDATE(), 104)
|
06.03.2012
|
105
|
SELECT CONVERT(VARCHAR, GETDATE(), 105)
|
06-03-2012
|
106
|
SELECT CONVERT(VARCHAR, GETDATE(), 106)
|
06 Mar 2012
|
107
|
SELECT CONVERT(VARCHAR, GETDATE(), 107)
|
Mar 06, 2012
|
110
|
SELECT CONVERT(VARCHAR, GETDATE(), 110)
|
03-06-2012
|
111
|
SELECT CONVERT(VARCHAR, GETDATE(), 111)
|
2012/03/06
|
Date and Time Conversions Using SQL Server
![Date and Time Conversions Using SQL Server]()
Reviewed by kamal kumar das
on
March 06, 2012
Rating:
No comments: