Here are some more date formats that does not
come standard in SQL Server as part of the CONVERT function.
|
Extended Date Formats
|
||
|
Date Format
|
SQL Statement
|
Sample Output
|
|
YY-MM-DD
|
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8)
AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-')
AS [YY-MM-DD]
|
99-01-24
|
|
YYYY-MM-DD
|
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-')
AS [YYYY-MM-DD]
|
1999-01-24
|
|
MM/YY
|
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] |
08/99
|
|
MM/YYYY
|
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS
[MM/YYYY]
|
12/2005
|
|
YY/MM
|
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]
|
99/08
|
|
YYYY/MM
|
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
|
2005/12
|
|
Month DD, YYYY 1
|
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12),
GETDATE(), 107), 9) AS [Month DD, YYYY]
|
July 04, 20061
|
|
Mon YYYY 1
|
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8)
AS [Mon YYYY]
|
Apr 2006 1
|
|
Month YYYY1
|
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS
VARCHAR(4)) AS [Month YYYY]
|
February 20061
|
|
DD Month 1
|
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM,
GETDATE()) AS [DD Month]
|
11 September1
|
|
Month DD 1
|
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS
VARCHAR(2)) AS [Month DD]
|
September 111
|
|
DD Month YY 1
|
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM,
GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month
YY]
|
19 February 72 1
|
|
DD Month YYYY 1
|
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM,
GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
|
11 September 2002 1
|
|
MM-YY
|
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] |
12/92
|
|
MM-YYYY
|
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS
[MM-YYYY]
|
05-2006
|
|
YY-MM
|
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS
[YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] |
92/12
|
|
YYYY-MM
|
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]
|
2006-05
|
|
MMDDYY
|
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS
[MMDDYY]
|
122506
|
|
MMDDYYYY
|
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '')
AS [MMDDYYYY]
|
12252006
|
|
DDMMYY
|
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS
[DDMMYY]
|
240702
|
|
DDMMYYYY
|
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '')
AS [DDMMYYYY]
|
24072002
|
|
Mon-YY 1
|
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), '
', '-') AS [Mon-YY]
|
Sep-02 1
|
|
Mon-YYYY 1
|
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8),
' ', '-') AS [Mon-YYYY]
|
Sep-2002 1
|
|
DD-Mon-YY1
|
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS
[DD-Mon-YY]
|
25-Dec-05 1
|
|
DD-Mon-YYYY 1
|
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-')
AS [DD-Mon-YYYY]
|
25-Dec-20051
|
1 To
make the month name in upper case, simply use the UPPER string function.
No comments:
Post a Comment