convert(char(8),getdate(),112) ' 오늘날짜 8자리
문자데이타를 날짜형 으로 옮기기
'2006-08-21 12:11:49' 이런식으로 날짜 사이에는 하이픈,점,공백이 들어 가야하고
시간에는 : 문자가 들어가야 함
convert(datetime,'2006-08-21 12:11:49')
112 같은 타입을 주면 안됨, 형식이 맞으면 자동으로 변경함
까다로움. 날짜형에 초기값을 줄때는 0 입력 이때는 숫자형으로 입력 되야함
날짜형 필드 reg 에서 년도 가져오기
CAST(DATEPART(YEAR,reg) AS VARCHAR(4)) as ryear
Option name in the popup list |
Inserted string (where Fld is the selected field) |
Float [6-digit] |
CONVERT(VARCHAR, Fld, 0) |
Float [8-digit Scientific] |
CONVERT(VARCHAR, Fld, 1) |
Float [16-digit Scientific] |
CONVERT(VARCHAR, Fld, 2) |
Other Numeric |
CONVERT(VARCHAR, Fld) |
Currency [9999.99] |
CONVERT(VARCHAR, Fld, 0) |
Currency [9,999.99] |
CONVERT(VARCHAR, Fld, 1) |
Currency [9999.9999] |
CONVERT(VARCHAR, Fld, 2) |
Date-Time [mm/dd/yy] |
CONVERT(VARCHAR, Fld, 1) |
Date-Time [mm/dd/yyyy] |
CONVERT(VARCHAR, Fld, 101 ) |
Date-Time [yy.mm.dd] |
CONVERT(VARCHAR, Fld, 2) |
Date-Time [yyyy.mm.dd] |
CONVERT(VARCHAR, Fld, 102) |
Date-Time [dd/mm/yy] |
CONVERT(VARCHAR, Fld, 3) |
Date-Time [dd/mm/yyyy] |
CONVERT(VARCHAR, Fld, 103 ) |
Date-Time [dd.mm.yy] |
CONVERT(VARCHAR, Fld, 4) |
Date-Time [dd.mm.yyyy] |
CONVERT(VARCHAR, Fld, 104 ) |
Date-Time [dd-mm-yy] |
CONVERT(VARCHAR, Fld, 5) |
Date-Time [dd-mm-yyyy] |
CONVERT(VARCHAR, Fld, 105) |
Date-Time [dd mon yy] |
CONVERT(VARCHAR, Fld, 6) |
Date-Time [dd mon yyyy] |
CONVERT(VARCHAR, Fld, 106) |
Date-Time [Mon dd, yy] |
CONVERT(VARCHAR, Fld, 7) |
Date-Time [Mon dd, yyyy] |
CONVERT(VARCHAR, Fld, 107) |
Date-Time [mm-dd-yy] |
CONVERT(VARCHAR, Fld, 10) |
Date-Time [mm-dd-yyyy] |
CONVERT(VARCHAR, Fld, 110 ) |
Date-Time [yy/mm/dd] |
CONVERT(VARCHAR, Fld, 11) |
Date-Time [yyyy/mm/dd] |
CONVERT(VARCHAR, Fld, 111 ) |
Date-Time [yymmdd] |
CONVERT(VARCHAR, Fld, 12) |
Date-Time [yyyymmdd] |
CONVERT(VARCHAR, Fld, 112) |
Date-Time [mon dd yyyy hh:miAM] |
CONVERT(VARCHAR, Fld, 0) |
Date-Time [mon dd yyyy hh:mi:ss:mmmAM] |
CONVERT(VARCHAR, Fld, 9) |
Date-Time [dd mon yyyy hh:mm:ss:mmm] |
CONVERT(VARCHAR, Fld, 13) |
Date-Time [hh:mm:ss] |
CONVERT(VARCHAR, Fld, 8) |
Date-Time [hh:mi:ss:mmm] |
CONVERT(VARCHAR, Fld, 14) |
Date-Time [yyyy-mm-dd hh:mi:ss] |
CONVERT(VARCHAR, Fld, 20) |
Date-Time [yyyy-mm-dd hh:mi:ss.mmm] |
CONVERT(VARCHAR, Fld, 21) |
Date-Time [yyyy-mm-ddThh:mm:ss:mmm] |
CONVERT(VARCHAR, Fld, 126 ) [출처] SQL 날짜 함수 변환|작성자 쿵푸팬다 |