Have an account?

--------------------------------------------------------------------

Silahkan jelajahi anggep aja Blog Sendiri

Senin, 02 Desember 2013

Mengatur Format Date di SQL Server Menggunakan CONVERT

Standard Date Formats
Date Format
SQL Statement
Sample Output
Mon DD YYYY HH:MIAM (or PM)
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Jan 1 2005 1:29PM
MM/DD/YY
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
11/23/98
MM/DD/YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
11/23/1998
YY.MM.DD
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
72.01.01
YYYY.MM.DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
1972.01.01
DD/MM/YY
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
19/02/72
DD/MM/YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
19/02/1972
DD.MM.YY
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
25.12.05
DD.MM.YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
25.12.2005
DD-MM-YY
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
24-01-98
DD-MM-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
24-01-1998
DD Mon YY
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
04 Jul 06
DD Mon YYYY
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
04 Jul 2006
Mon DD, YY
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Jan 24, 98
Mon DD, YYYY
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Jan 24, 1998
HH:MM:SS
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM)
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
Apr 28 2006 12:32:29:253PM
MM-DD-YY
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
01-01-06
MM-DD-YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
01-01-2006
YY/MM/DD
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
98/11/23
YYYY/MM/DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
1998/11/23
YYMMDD
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
980124
YYYYMMDD
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
19980124
DD Mon YYYY
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
28 Apr 2006 00:34:55:190
    HH:MI:SS:MMM(24H)
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
1972-02-19 06:35:24.489
 
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
28 Apr 2006 12:39:32:429AM
DD/MM/YYYY HH:MI:SS:MMMAM
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
28/04/2006 12:39:32:429AM
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
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9)
AS [Month DD, YYYY]
July 04, 2006
Mon YYYY
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8 ) AS [Mon YYYY]
Apr 2006
Month YYYY
SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS
[Month YYYY]
February 2006
DD Month
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month]
11 September
Month DD
SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]
September 11
DD Month YY
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
DD Month YYYY
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
11 September 2002
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
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-’) AS
[Mon-YY]
Sep-02
Mon-YYYY
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-’) AS
[Mon-YYYY]
Sep-2002
DD-Mon-YY
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-’) AS [DD-Mon-YY]
25-Dec-05
DD-Mon-YYYY
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-’) AS [DD-Mon-YYYY]
25-Dec-2005
Diposting oleh Unknown 0 komentar
Label: , , , ,
Langganan: Postingan (Atom)