[MSSQL] 날짜, 시간 변환(CONVERT) 및 시간 비교

프로젝트 중 고객사에서 개발 요구사항이 하나 들어왔다.

"야간 작업 때문에 00시 ~ 07시까지는 전날 날짜로 그 이후 시간에는 오늘 날짜로 보이도록 해주세요."

WEB화면에서 초기값이 오늘 날짜로 보여지고 있는 날짜 선택 필드가 있는데 야간 작업 때문에 위와 같이 변경을 요청하였다. 사유: 날짜 변경이 귀찮음ㅎㅎㅎㅎ.. 가독성을 위해 저는 수정하러 총총....

 

 

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16 

CAST 및 CONVERT(Transact-SQL) - SQL Server

SQL Server홈페이지에 날짜, 시간 변환하는 Convert함수에 대해 나와있는데 내 기준 너어무 복잡....함...ㅎ..

멍때리며 스크롤만 쭈욱..ㅎ...

 

기존 Oracle사용 시에는 TO_CHAR를 이용해 빠르게 변경 가능하였는데 MSSQL은 Oracle만큼이나 손에 익은게 아니여서 그런지 날짜, 시간 변환 방법에 대해 정리해 보았다.

번호	쿼리	결과	포맷
0	CONVERT(CHAR(19), GETDATE(), 0)	08  2 2023 10:27AM	MM DD YYYY H:MM
1	CONVERT(CHAR(10), GETDATE(), 1)	08/02/23	MM/DD/YY
2	CONVERT(CHAR(8), GETDATE(), 2)	23.08.02	YY.MM.DD
3	CONVERT(CHAR(8), GETDATE(), 3)	02/08/23	DD/MM/YY
4	CONVERT(CHAR(8), GETDATE(), 4)	02.08.23	DD.MM.YY
5	CONVERT(CHAR(8), GETDATE(), 5)	02-08-23	DD-MM-YY
6	CONVERT(CHAR(8), GETDATE(), 6)	02 08 23	DD MM YY
7	CONVERT(CHAR(9), GETDATE(), 7)	08 02, 23	MM DD, YY
8	CONVERT(CHAR(8), GETDATE(), 8)	10:30:12	HH:MM:SS
9	CONVERT(CHAR(26), GETDATE(), 9)	08  2 2023 10:30:23:810AM	MM DD YYYY H:MM:SS.MS
10	CONVERT(CHAR(8), GETDATE(), 10)	08-02-23	MM-DD-YY
11	CONVERT(CHAR(8), GETDATE(), 11)	23/08/02	YY/MM/DD
12	CONVERT(CHAR(6), GETDATE(), 12)	230802	YYMMDD
13	CONVERT(CHAR(24), GETDATE(), 13)	02 08 2023 10:32:19:813	DD MM YYYY HH:MM:SS.MS
14	CONVERT(CHAR(12), GETDATE(), 14)	10:35:07:067	HH:MM:SS:MS
20	CONVERT(CHAR(19), GETDATE(), 20)	2023-08-02 10:35:33	YYYY-MM-DD HH:MM:SS
21	CONVERT(CHAR(23), GETDATE(), 21)	2023-08-02 10:35:49.483	YYYY-MM-DD HH:MM:SS.MS
22	CONVERT(CHAR(20), GETDATE(), 22)	08/02/23 10:36:34 AM	MM/DD/YY H:M:S
23	CONVERT(CHAR(10), GETDATE(), 23)	2023-08-02	YYYY-MM-DD
24	CONVERT(CHAR(8), GETDATE(), 24)	10:39:30	HH:MM:SS
25	CONVERT(CHAR(23), GETDATE(), 25)	2023-08-02 10:39:39.143	YYYY-MM-DD HH:MM:SS.MS
100	CONVERT(CHAR(19), GETDATE(), 100)	08  2 2023 10:39AM	MM D YYYY H:MM
101	CONVERT(CHAR(10), GETDATE(), 101)	08/02/2023	MM/DD/YYYY
102	CONVERT(CHAR(10), GETDATE(), 102)	2023.08.02	YYYY.MM.DD
103	CONVERT(CHAR(10), GETDATE(), 103)	02/08/2023	DD/MM/YYYY
104	CONVERT(CHAR(10), GETDATE(), 104)	02.08.2023	DD.MM.YYYY
105	CONVERT(CHAR(10), GETDATE(), 105)	02-08-2023	DD-MM-YYYY
106	CONVERT(CHAR(11), GETDATE(), 106)	02 08 2023	DD MM YYYY
107	CONVERT(CHAR(12), GETDATE(), 107)	08 02, 2023	MM DD, YYYY
108	CONVERT(CHAR(8), GETDATE(), 108)	10:41:24	HH:MM:SS
109	CONVERT(CHAR(26), GETDATE(), 109)	08  2 2023 10:41:36:627AM	MM D YYYY H:MM:DD.MS
110	CONVERT(CHAR(10), GETDATE(), 110)	08-02-2023	MM-DD-YYYY
111	CONVERT(CHAR(10), GETDATE(), 111)	2023/08/02	YYYY/MM/DD
112	CONVERT(CHAR(8), GETDATE(), 112)	20230802	YYYYMMDD
113	CONVERT(CHAR(24), GETDATE(), 113)	02 08 2023 10:42:26:623	DD MM YYYY HH:MM:DD.MS
114	CONVERT(CHAR(12), GETDATE(), 114)	10:42:35:860	HH:MM:DD:MS
120	CONVERT(CHAR(19), GETDATE(), 120)	2023-08-02 10:42:45	YYYY-MM-DD HH:MM:SS
121	CONVERT(CHAR(23), GETDATE(), 121)	2023-08-02 10:42:54.243	YYYY-MM-DD HH:MM:SS.MS
126	CONVERT(CHAR(23), GETDATE(), 126)	2023-08-02T10:43:06.920	YYYY-MM-DDT HH:MM:SS
127	CONVERT(CHAR(23), GETDATE(), 127)	2023-08-02T10:43:15.203	YYYY-MM-DDT HH:MM:SS
131	CONVERT(CHAR(25), GETDATE(), 131)	16/01/1445 10:43:24:660AM

 

위의 날짜, 시간 변환 방법을 참고하여 개발 요구사항을 다음과 같이 해결하였다.

 

1. 우선 현재 시간을 원하는 포맷에 맞게 추출한다.

 SELECT CONVERT(CHAR(8), GETDATE(), 8) AS TIME FROM DUAL --현재 시간

 

2.  요구한 시간비교를 위해서는 시간만 필요하므로 SUBSTRING함수를 이용해 시간만 자른다.

SELECT CONVERT(CHAR(8), GETDATE(), 8) AS TIME  --현재 시간
     , SUBSTRING(CONVERT(CHAR (8), GETDATE(), 8),1,2) AS HOUR -- 원하는 시간 만큼 자르기
  FROM DUAL

 ** 1, 2번 결과값 **

현재시간, 원하는 시간 만큼 자른 결과값

 

이제 원하는 시간을 비교할 수 있는 준비가 되었다.

최종적으로 시간을 비교해서 충족시키기 전에 전날 날짜를 계산하자.

 

3. 전날 날짜 계산 쿼리 

SQL Server에서의 날짜, 시간 계산은 DATEADD함수를 쓴다.

SELECT DATEADD(DAY, -1, GETDATE()) AS "현재로 부터 하루 전"
     , DATEADD(DAY, 1, GETDATE()) AS "현재로 부터 하루 후"
  FROM DUAL;

 

위의 포스팅을 참조하여 오늘부터 하루전 날짜를 다음과 같이 계산하였다. (YYYY.MM.DD포맷 사용)

SELECT CONVERT(CHAR(10), DATEADD(DAY,-1,GETDATE()),102) AS "TODAY-1" FROM DUAL;

** 결과값 **

오늘 날짜와 히루전 날짜

 

4. 00시 ~ 07시까지는 전날 날짜로 그 이후 시간에는 오늘 날짜로 보이는 쿼리를 CASE문과 BETWEEN함수를 통해 짜준다. (YYYY.MM.DD포맷 사용)

 SELECT CASE WHEN SUBSTRING(CONVERT(CHAR(8), GETDATE(), 8),1,2) BETWEEN '00' AND '07' THEN CONVERT(CHAR(10), DATEADD(DAY,-1,GETDATE()),102)
             ELSE CONVERT(CHAR (10), GETDATE(), 102)
        END AS DATE; --현재 시간 오전 9:49분

** 결과값 **

DATE: 최종 결과값

현재 시간이 9시 49분이라 결과값은 오늘날짜로 나오고 있다.

 

 

시간만 비교하는 경우는 사실상 00시00분 ~ 7시 59분까지 전날 일자로 보이기 때문에 분까지 처리하고 싶다면,

BETWEEN함수 사용시 '0000' 부터 '0700' 로 비교해야 한다.

 

1. SUBSTRING함수 사용시 길이를 조절 및 REPLACE함수를 통해 포맷을 제거

 SELECT REPLACE(SUBSTRING(CONVERT(CHAR (8), GETDATE(), 8),1,5),':','') AS REMOVE_TIME_FORMAT
   FROM DUAL;

** 결과값 **

기존 시간과 포맷 제거한 시간

2. 그 다음은 위와 동일하게 처리 하면 된다!

  SELECT CASE WHEN REPLACE(SUBSTRING(CONVERT(CHAR (8), GETDATE(), 8),1,5),':','') BETWEEN '0000' AND '0700' THEN CONVERT(CHAR (10), DATEADD(DAY,-1,GETDATE()),102)
              ELSE CONVERT(CHAR (10), GETDATE(), 102)
         END AS DATE;