Convert a date 를 yyyymmdd format 형식으로 변경

MS-SQL의 getDate()함수를 이용해서, 현재 날짜를 YYYYMMDD 형태로 만들고 싶을때

다음과 같이 사용하면 된니다.

select CONVERT(varchar(8), GETDATE(), 112) today

결과

20150730

참고: http://dba.stackexchange.com/questions/42395/convert-a-date-to-yyyymmdd-format

---

YYYYMMDD 에서 => 년, 월, 일, 요일 뽑아내기

SELECT DATEPART(year, '20161213') AS BIZ_YEAR; -- 2016

SELECT DATEPART(month, '20161213') AS BIZ_MONTH; -- 12

SELECT DATEPART(day, '20161213') AS BIZ_DAY;-- 13

SELECT DATEPART(weekday, '20161213') AS WEEK_NO; -- 3 (1:Sun, 2:Mon, Sat:7)

---

SQL Server CONVERT() Function

Syntax

CONVERT(data_type(length),expression,style)

Value	Description
data_type(length)	Specifies the target data type (with an optional length)
expression	Specifies the value to be converted
style	Specifies the output format for the date/time (see table below)

The style value can be one of the following values:

Without century	With century	Input/Output	Standard
-	0 or 100	mon dd yyyy hh:miAM (or PM)	Default
1	101	1 = mm/dd/yy 101 = mm/dd/yyyy	USA
2	102	2 = yy.mm.dd 102 = yyyy.mm.dd	ANSI
3	103	3 = dd/mm/yy 103 = dd/mm/yyyy	British/French
4	104	4 = dd.mm.yy 104 = dd.mm.yyyy	German
5	105	5 = dd-mm-yy 105 = dd-mm-yyyy	Italian
6	106	6 = dd mon yy 106 = dd mon yyyy	-
7	107	7 = Mon dd, yy 107 = Mon dd, yyyy	-
8	108	hh:mm:ss	-
-	9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
10	110	10 = mm-dd-yy 110 = mm-dd-yyyy	USA
11	111	11 = yy/mm/dd 111 = yyyy/mm/dd	Japan
12	112	12 = yymmdd 112 = yyyymmdd	ISO
-	13 or 113	dd mon yyyy hh:mi:ss:mmm (24h)	Europe default + millisec
14	114	hh:mi:ss:mmm (24h)	-
-	20 or 120	yyyy-mm-dd hh:mi:ss (24h)	ODBC canonical
-	21 or 121	yyyy-mm-dd hh:mi:ss.mmm (24h)	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
-	126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)	ISO8601
-	127	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)	ISO8601 with time zone Z
-	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

---

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-14
11-04-2014
04 Nov 14
04 Nov 2014
04 Nov 2014 11:45:34:243

출처: http://www.w3schools.com/sql/func_convert.asp