DB관련/SQL Server

날짜 포멧 Date format - MSSQL 2012

saltdoll 2017. 6. 29. 00:56
반응형

SQL Server 2012 FORMAT 명령 vs CONVERT 명령


SQL Server 2012 FORMAT Function vs. CONVERT Function

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERTA?function and the corresponding syntax using the new SQL Server 2012 FORMAT string function. Following the standard date formats are some extended date formats that are often asked by SQL Server developers, together with the corresponding syntax as well using the new SQL Server 2012 FORMAT string function.

It is worth to note that the outputs of these date formats are of VARCHAR data types already and not of DATETIMEA?data type. With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIMEA?value.

The SQL statements used below to return the different date formats use the SYSDATETIME() date function. The SYSDATETIME() function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The SYSDATETIME() function used below can be replaced by the GETDATE() or GETUTCDATE() functions. The results will be the same unless the date format includes the nanosecond portion of the time.

To make the date format results consistent, the date and time used to generate the sample output is June 8, 2011 1:30:45.9428675 PM.

Standard CONVERT Date Formats
Date FormatFORMAT FunctionCONVERT FunctionSample Output

Mon DD YYYY1
HH:MIAM (or PM)

SELECT FORMAT(SYSDATETIME(), 'Mon d yyyy h:mmtt')SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)

Jun 8 2011 1:30PM1

MM/DD/YYSELECT FORMAT(SYSDATETIME(), 'MM/dd/yy') AS [MM/DD/YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]06/08/11
MM/DD/YYYYSELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy') AS [MM/DD/YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]06/08/2011
YY.MM.DDSELECT FORMAT(SYSDATETIME(), 'yy.MM.dd') AS [YY.MM.DD]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]11.06.08
YYYY.MM.DDSELECT FORMAT(SYSDATETIME(), 'yyyy.MM.dd') AS [YYYY.MM.DD]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]2011.06.08
DD/MM/YYSELECT FORMAT(SYSDATETIME(), 'dd/MM/yy') AS [DD/MM/YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]08/06/11
DD/MM/YYYYSELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy') AS [DD/MM/YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]08/06/2011
DD.MM.YYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
DD.MM.YYYYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD-MM-YYSELECT FORMAT(SYSDATETIME(), 'dd-MM-yy') AS [DD-MM-YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]08-06-11
DD-MM-YYYYSELECT FORMAT(SYSDATETIME(), 'dd-MM-yyyy') AS [DD-MM-YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]08-06-2011

DD Mon YY1

SELECT FORMAT(SYSDATETIME(), 'dd MMM yy') AS [DD MON YY]SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]

08 Jun 111

DD Mon YYYY1

SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy') AS [DD MON YYYY]SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]

08 Jun 20111

Mon DD, YY1

SELECT FORMAT(SYSDATETIME(), 'MMM dd, yy') AS [Mon DD, YY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]

Jun 08, 111

Mon DD, YYYY1

SELECT FORMAT(SYSDATETIME(), 'MMM dd, yyyy') AS [Mon DD, YYYY]SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]

Jun 08, 20111

HH:MM:SSSELECT FORMAT(SYSDATETIME(), 'HH:mm:ss')SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)		13:30:45

Mon D YYYY H:MI:SS.NNNNNNNAM (or PM)1

SELECT FORMAT(SYSDATETIME(), 'MMM d yyyy h:mm:ss.ffffffftt')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)

Jun 8 2011 1:30:45.9428675PM1

MM-DD-YYSELECT FORMAT(SYSDATETIME(), 'MM-dd-yy') AS [MM-DD-YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]06-08-11
MM-DD-YYYYSELECT FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]06-08-2011
YY/MM/DDSELECT FORMAT(SYSDATETIME(), 'yy/MM/dd') AS [YY/MM/DD]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]11/06/08
YYYY/MM/DDSELECT FORMAT(SYSDATETIME(), 'yyyy/MM/dd') AS [YYYY/MM/DD]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]2011/06/08
YYMMDDSELECT FORMAT(SYSDATETIME(), 'yyMMdd') AS [YYMMDD]SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]110608
YYYYMMDDSELECT FORMAT(SYSDATETIME(), 'yyyyMMdd') AS [YYYYMMDD]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]20110608

DD Mon YYYY HH:MM:SS.NNNNNNN(24h)1

SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy HH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)		08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss.fffffff') AS [HH:MI:SS:MMM(24H)]SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)]
SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]		13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h)SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss')SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AMSELECT FORMAT(SYSDATETIME(), 'MM/dd/yy h:mm:ss tt')SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)06/08/11 1:30:45 PM
YYYY-MM-DDSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)2011-06-091
HH:MI:SS (24h)SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss')SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNNSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNNSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-ddTHH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)2011-06-08T13:30:45.9428675

DD Mon YYYY HH:MI:SS.NNNNNNNAM1

SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy h:mm:ss.ffffffftt')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAMSELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy h:mm:ss.ffffffftt')SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)08/06/2011 1:30:45.9428675PM


Here are some more date formats that does not come standard in SQL Server as part of theA?CONVERTA?function, together with the corresponding FORMAT function.

Extended Date Formats
Date FormatFORMAT FunctionCONVERT FunctionSample Output
YY-MM-DDSELECT FORMAT(SYSDATETIME(), 'yy-MM-dd') AS [YY-MM-DD]
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD]
11-06-08
YYYY-MM-DDSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd') AS [YYYY-MM-DD]
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), '/', '-') AS [YYYY-MM-DD]
2011-06-08
YYYY-M-DSELECT FORMAT(SYSDATETIME(), 'yyyy-M-d') AS [YYYY-M-D]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D]2011-6-8
YY-M-DSELECT FORMAT(SYSDATETIME(), 'yy-M-d') AS [YY-M-D]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D]11-6-8
M-D-YYYYSELECT FORMAT(SYSDATETIME(), 'M-d-yyyy') AS [M-D-YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY]6-8-2011
M-D-YYSELECT FORMAT(SYSDATETIME(), 'M-d-yy') AS [M-D-YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY]6-8-11
D-M-YYYYSELECT FORMAT(SYSDATETIME(), 'd-M-yyyy') AS [D-M-YYYY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY]8-6-2011
D-M-YYSELECT FORMAT(SYSDATETIME(), 'd-M-yy') AS [D-M-YY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY]8-6-11
YY-MMSELECT FORMAT(SYSDATETIME(), 'yy-MM') AS [YY-MM]SELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM]		11-06
YYYY-MMSELECT FORMAT(SYSDATETIME(), 'yyyy-MM') AS [YYYY-MM]SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM]2011-06
YY-MSELECT FORMAT(SYSDATETIME(), 'yy-M') AS [YY-M]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M]11-6
YYYY-MSELECT FORMAT(SYSDATETIME(), 'yyyy-M') AS [YYYY-M]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M]2011-6
MM-YYSELECT FORMAT(SYSDATETIME(), 'MM-yy') AS [MM-YY]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY]		06-11
MM-YYYYSELECT FORMAT(SYSDATETIME(), 'MM-yyyy') AS [MM-YYYY]SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY]06-2011
M-YYSELECT FORMAT(SYSDATETIME(), 'M-yy') AS [M-YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY]6-11
M-YYYYSELECT FORMAT(SYSDATETIME(), 'M-yyyy') AS [M-YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY]6-2011
MM-DDSELECT FORMAT(SYSDATETIME(), 'MM-dd') AS [MM-DD]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD]06-08
DD-MMSELECT FORMAT(SYSDATETIME(), 'dd-MM') AS [DD-MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM]08-06
M-DSELECT FORMAT(SYSDATETIME(), 'M-d') AS [M-D]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D]6-8
D-MSELECT FORMAT(SYSDATETIME(), 'd-M') AS [D-M]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M]8-6
M/D/YYYYSELECT FORMAT(SYSDATETIME(), 'M/d/yyyy') AS [M/D/YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY]6/8/2011
M/D/YYSELECT FORMAT(SYSDATETIME(), 'M/d/yy') AS [M/D/YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY]6/8/11
D/M/YYYYSELECT FORMAT(SYSDATETIME(), 'd/M/yyyy') AS [D/M/YYYY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY]8/6/2011
D/M/YYSELECT FORMAT(SYSDATETIME(), 'd/M/yy') AS [D/M/YY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY]8/6/11
YYYY/M/DSELECT FORMAT(SYSDATETIME(), 'yyyy/M/d') AS [YYYY/M/D]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D]2011/6/8
YY/M/DSELECT FORMAT(SYSDATETIME(), 'yy/M/d') AS [YY/M/D]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D]11/6/8
MM/YYSELECT FORMAT(SYSDATETIME(), 'MM/yy') AS [MM/YY]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY]06/11
MM/YYYYSELECT FORMAT(SYSDATETIME(), 'MM/yyyy') AS [MM/YYYY]SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY]06/2011
M/YYSELECT FORMAT(SYSDATETIME(), 'M/yy') AS [M/YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY]6/11
M/YYYYSELECT FORMAT(SYSDATETIME(), 'M/yyyy') AS [M/YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY]6/2011
YY/MMSELECT FORMAT(SYSDATETIME(), 'yy/MM') AS [YY/MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM]11/06
YYYY/MMSELECT FORMAT(SYSDATETIME(), 'yyyy/MM') AS [YYYY/MM]SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM]2011/06
YY/MSELECT FORMAT(SYSDATETIME(), 'yy/M') AS [YY/M]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M]11/6
YYYY/MSELECT FORMAT(SYSDATETIME(), 'yyyy/M') AS [YYYY/M]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M]2011/6
MM/DDSELECT FORMAT(SYSDATETIME(), 'MM/dd') AS [MM/DD]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD]06/08
DD/MMSELECT FORMAT(SYSDATETIME(), 'dd/MM') AS [DD/MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM]08/06
M/DSELECT FORMAT(SYSDATETIME(), 'M/d') AS [M/D]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D]6/8
D/MSELECT FORMAT(SYSDATETIME(), 'd/M') AS [D/M]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M]8/6
MM.DD.YYYYSELECT FORMAT(SYSDATETIME(), 'MM.dd.yyyy') AS [MM.DD.YYYY]SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY]06.08.2011
MM.DD.YYSELECT FORMAT(SYSDATETIME(), 'MM.dd.yy') AS [MM.DD.YY]SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY]06.08.11
M.D.YYYYSELECT FORMAT(SYSDATETIME(), 'M.d.yyyy') AS [M.D.YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY]6.8.2011
M.D.YYSELECT FORMAT(SYSDATETIME(), 'M.d.yy') AS [M.D.YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY]6.8.11
DD.MM.YYYYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD.MM.YYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
D.M.YYYYSELECT FORMAT(SYSDATETIME(), 'd.M.yyyy') AS [D.M.YYYY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY]8.6.2011
D.M.YYSELECT FORMAT(SYSDATETIME(), 'd.M.yy') AS [D.M.YY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY]8.6.11
YYYY.M.DSELECT FORMAT(SYSDATETIME(), 'yyyy.M.d') AS [YYYY.M.D]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D]2011.6.8
YY.M.DSELECT FORMAT(SYSDATETIME(), 'yy.M.d') AS [YY.M.D]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D]11.6.8
MM.YYYYSELECT FORMAT(SYSDATETIME(), 'MM.yyyy') AS [MM.YYYY]SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY]06.2011
MM.YYSELECT FORMAT(SYSDATETIME(), 'MM.yy') AS [MM.YY]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY]06.11
M.YYYYSELECT FORMAT(SYSDATETIME(), 'M.yyyy') AS [M.YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY]6.2011
M.YYSELECT FORMAT(SYSDATETIME(), 'M.yy') AS [M.YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY]6.11
YYYY.MMSELECT FORMAT(SYSDATETIME(), 'yyyy.MM') AS [YYYY.MM]SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM]2011.06
YY.MMSELECT FORMAT(SYSDATETIME(), 'yy.MM') AS [YY.MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM]11.06
YYYY.MSELECT FORMAT(SYSDATETIME(), 'yyyy.M') AS [YYYY.M]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M]2011.6
YY.MSELECT FORMAT(SYSDATETIME(), 'yy.M') AS [YY.M]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M]11.6
MM.DDSELECT FORMAT(SYSDATETIME(), 'MM.dd') AS [MM.DD]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD]06.08
DD.MMSELECT FORMAT(SYSDATETIME(), 'dd.MM') AS [DD.MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM]08.06
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY]06082011
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY]060811
DDMMYYYYSELECT FORMAT(SYSDATETIME(), 'ddMMyyyy') AS [DDMMYYYY]SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY]08062011
DDMMYYSELECT FORMAT(SYSDATETIME(), 'ddMMyy') AS [DDMMYY]SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]080611
MMYYYYSELECT FORMAT(SYSDATETIME(), 'MMyyyy') AS [MMYYYY]SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY]062011
MMYYSELECT FORMAT(SYSDATETIME(), 'MMyy') AS [MMYY]SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY]0611
YYYYMMSELECT FORMAT(SYSDATETIME(), 'yyyyMM') AS [YYYYMM]SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM]201106
YYMMSELECT FORMAT(SYSDATETIME(), 'yyMM') AS [YYMM]SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM]1106

Month DD, YYYY1

SELECT FORMAT(SYSDATETIME(), 'MMMM dd, yyyy') AS [Month DD, YYYY]SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY]June 08, 2011A?1

Mon YYYY1

SELECT FORMAT(SYSDATETIME(), 'MMM yyyy') AS [Mon YYYY]SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY]Jun 2011A?1

Month YYYY1

SELECT FORMAT(SYSDATETIME(), 'MMMM yyyy') AS [Month YYYY]SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY]June 2011A?1

DD Month1

SELECT FORMAT(SYSDATETIME(), 'dd MMMM') AS [DD Month]SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month]08 JuneA?1

Month DD1

SELECT FORMAT(SYSDATETIME(), 'MMMM dd') AS [Month DD]SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD]June 08A?1

DD Month YY1

SELECT FORMAT(SYSDATETIME(), 'dd MMMM yy') AS [DD Month YY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' ' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY]08 June 11A?1

DD Month YYYY1

SELECT FORMAT(SYSDATETIME(), 'dd MMMM yyyy') AS [DD Month YYYY]SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY]08 June 2011A?1

Mon-YY1

SELECT FORMAT(SYSDATETIME(), 'MMM-yy') AS [Mon-YY]SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY]Jun-08A?1

Mon-YYYY1

SELECT FORMAT(SYSDATETIME(), 'MMM-yyyy') AS [Mon-YYYY]SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY]Jun-2011A?1

DD-Mon-YY1

SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yy') AS [DD-Mon-YY]SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY]08-Jun-11A?1

DD-Mon-YYYY1

SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yyyy') AS [DD-Mon-YYYY]SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY]08-Jun-2011A?1

1To make the month name in upper case, simply use the UPPER string function.



참고: http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

반응형
저작자표시 비영리 변경금지

'DB관련 > SQL Server' 카테고리의 다른 글

sqlcmd 이용한 쿼리 확인하기 (Management Studio가 없을때)  (0) 2017.12.09
MSSQL 컬럼 변경 ( 추가, 삭제, 속성변경, 컬럼명 변경 )  (0) 2017.10.12
MSSQL 2000~2008 DB 테이블 명세서 작성해주는 출력 쿼리  (0) 2017.06.30
[MSSQL] 문자열 자르기 - 구분자로 자르기, 길이로 자르기  (0) 2017.06.30
날짜 포멧 convert명령 - MSSQL 2008 이상  (0) 2017.06.29
MSSQL Server Version 별 제품  (0) 2017.05.16
MSSQL 특정 테이블 백업하기  (0) 2017.01.26
MSSQL 소숫점 자리수 맞추기  (0) 2016.02.20
도움이 되셨다면 하트모양의 "♡ 공감"을 눌러주시면 큰 격려가 됩니다.
(로그인하지 않으셔도 가능)

'DB관련/SQL Server'의 다른글

  • 현재글날짜 포멧 Date format - MSSQL 2012

관련글

댓글

티스토리툴바