[mssql] StringType시간 데이터 >> 시계열 변환 함수 정리

※ 참고 링크

※ SSMS에서 사용하는 함수를 드래그한 후 Shift +  F1을 누르면 함수 공식 Document 홈페이지로 이동한다.

CAST 및 CONVERT(Transact-SQL) - SQL Server

1. STRING(YYYYmmddHHMMSS/YYYYmmddHHMMSSf) -> DATETIME

- Type이 Datetime 형식인 경우, STUFF를 제외하고 CONVERT한다. (STUFF함수는 밑에 설명)

- 이 외에도 CAST함수를 사용해서 변환하는 방법도 있다.

-- 1. YYYYmmddHHMMSS -> datetime 변환
Declare @DateTime nvarchar(20)
SET @DateTime = '20210201100024'
 
SELECT 
  CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 112) as DateTime
 
 
-- 2. YYYYmmddHHMMSSf (밀리초가 있는 경우) -> datetime 변환
Declare @DateTime nvarchar(20)
SET @DateTime = '20210201100024351'
 
SELECT 
  CONVERT(datetime, STUFF(STUFF(STUFF(STUFF(@DateTime,15,0,'.'), 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 112) as DateTime

- 1, 2번의 가장 큰 차이는 밀리초의 유뮤, 그리고 밀리초가 있는 경우에는 구분할 수 있도록 HHMMSS뒤에 STUFF함수를 이용해서 '.'을 삽입하는 것이다.

 

- CONVERT시 CONVERT하려고 하는 Datetime의 스타일을 지정할 수 있다. 

 

 

Declare @DateTime nvarchar(20)
SET @DateTime = '20210201100024'
 
SELECT 
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 101) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 102) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 103) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 104) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 105) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 106) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 107) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 108) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 109) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 110) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 111) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 112) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 113) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 114) as DateTime,
    CONVERT(DATETIME, STUFF(STUFF(STUFF(@DateTime, 13, 0, ':'), 11, 0, ':'), 9, 0, ' '), 120) as DateTime

- 변환 결과 데이터의 형식은 전부 동일하게 나왔다. 어떻게 사용하는 건지 확실하게 모르겠다.

 

2. String(YYYYmmdd) -> DATETIME

- YYYYmmdd의 경우 따로 구분자를 입력하지 않고 CONVERT할 수 있다.

- 아래의 결과는 전부 동일하다.

Declare @DateTime2 nvarchar(20)
SET @DateTime2 = '20210201'
 
SELECT 
    CONVERT(DATETIME, @DateTime2),
    CONVERT(DATETIME, @DateTime2, 101),
    CONVERT(DATETIME, @DateTime2, 102),
    CONVERT(DATETIME, @DateTime2, 103),
    CONVERT(DATETIME, @DateTime2, 104),
    CONVERT(DATETIME, @DateTime2, 105),
    CONVERT(DATETIME, @DateTime2, 106),
    CONVERT(DATETIME, @DateTime2, 107),
    CONVERT(DATETIME, @DateTime2, 108),
    CONVERT(DATETIME, @DateTime2, 109),
    CONVERT(DATETIME, @DateTime2, 110),
    CONVERT(DATETIME, @DateTime2, 111),
    CONVERT(DATETIME, @DateTime2, 112),
    CONVERT(DATETIME, @DateTime2, 113),
    CONVERT(DATETIME, @DateTime2, 114),
    CONVERT(DATETIME, @DateTime2, 120)
    
    ----------------------------------
    2021-02-01 00:00:00.000

 

3. STUFF

- 다른 문자열에 문자열을 삽입하는 함수. 이 함수는 지정된 시작 위치와 문자 수에 따라 첫 번째 문자열의 문자를 삭제하고 두 번째 문자열을 시작 위치에 삽입한다.

    - character_expression : 변환할 문자

    - start : 문자열 변환 시작위치

    - length : 문자열 변환을 몇 번째 문자까지 하는지에 대한 길이

    - replaceWith_expression : 대체할 문자

STUFF ( character_expression , start , length , replaceWith_expression ) 
 
SELECT STUFF('abcdef', 2, 3, 'ijklmn');  
GO  
 
---------   
aijklmnef   
  
(1 row(s) affected)  

 

- 지금까지의 지식으로는 datetime형식으로 변환할 수 있는 데이터는 년월일(YYYYmmdd) 혹은 년월일시간(YYYYmmddHHMMSS)이다. 년월일+시간(HH)데이터만 있는 경우 e.g. YYYYmmddHH 데이터는 datetime 형식으로 변환할 수 없다는 것이 지금까지의 결론이다.