DB

[MS-SQL] 시간 데이터를 시/분/초 추출하는 방법

떠돌이개발자

예전에 기록하지 않았던 것들에 대한 기록을 조금씩 시작해봅니다.

GETDATE()를 이용하여 현재 날짜 시간 정보에서 시간에 대한 정보만 가져오는 방법과 시분초로 쪼개서 가져오는 방법들에 대해 기록합니다.

 

현재날짜 시간을 가져올 수 있는 함수는 다음과 같은 종류가 있어요.

GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), GETUTCDATE()

SELECT문으로 조회를 해보면

SELECT 
	GETDATE() AS GETDATE
    ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
	,SYSDATETIME() AS SYSDATETIME
    ,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
    ,SYSUTCDATETIME() AS SYSUTCDATETIME 
    ,GETUTCDATE() AS GETUTCDATE

다음과 같은 결과를 얻을 수 있어요. CURRENT_TIMESTAMP 키워드의 경우 알리아스(AS)를 줄 때 동일한 이름로 별명을 지정할 수 없음으로 대괄호[ ]를 이용하여 묶어주면 동일한 이름으로 별명을 지정할 수 있어요.


 

현재 날짜 시간을 가져오는 방법

SELECT GETDATE()

--실행결과
2021-12-15 16:52:43.883

 

현재 날짜 시간에서 날짜만 가져오는 방법

SELECT CONVERT(date,GETDATE())

--실행결과
2021-12-15

 

현재 날짜 시간에서 시간만 가져오는 방법

SELECT CONVERT(time,GETDATE())

--실행결과
16:52:43.8830000

 

문자열을 시간으로 변환하는 방법

날짜 계산을 할때는 문자열을 시간으로 변환해야가능합니다.

SELECT CONVERT(time, '07:00')

--실행결과
07:00:00.0000000

 

현재 날짜 시간에서 시분 값만 가져오는 방법

SELECT CONVERT (VARCHAR(5), CONVERT(time,GETDATE()))

--실행결과
17:05

 

현재 날짜 시분초값만 가져오는 방법

마이크로 초 단위는 버립니다.

SELECT CONVERT (VARCHAR(8), CONVERT(time,GETDATE()))

--실행결과
17:06:21

 

문자열에서 년 월 일 가져오는 방법

시간으로 변환이 가능한 문자열이라면 날짜 정보를 가져올 수 있어요.

SELECT YEAR('2021-12-15 17:15:45');
SELECT MONTH('2021-12-15 17:15:45');
SELECT DAY('2021-12-15 17:15:45');

 --실행결과
 2021
 12
 15

 

현재시간을 기준으로 시간 범위를 지정하여 내가 지정한 코드값을 가져오는 방법

이 방법은 15분단위 기준으로 체크하여 코드값을 리턴하는 쿼리입니다.

SELECT CASE WHEN CONVERT(time,GETDATE()) <= CONVERT(time, '07:00') OR CONVERT(time,GETDATE()) < CONVERT(time, '07:15') THEN '10'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '07:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '07:30') THEN '12'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '07:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '07:45') THEN '14'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '07:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:00') THEN '16'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:15') THEN '18'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:30') THEN '20'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:45') THEN '22'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:00') THEN '24'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:15') THEN '26'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:30') THEN '28'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:45') THEN '30'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:00') THEN '32'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:15') THEN '34'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:30') THEN '36'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:45') THEN '38'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:00') THEN '40'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:15') THEN '42'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:30') THEN '44'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:45') THEN '46'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:00') THEN '48'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:15') THEN '50'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:30') THEN '52'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:45') THEN '54'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:00') THEN '56'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:15') THEN '58'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:30') THEN '60'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:45') THEN '62'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:00') THEN '64'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:15') THEN '66'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:30') THEN '68'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:45') THEN '70'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:00') THEN '72'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:15') THEN '74'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:30') THEN '76'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:45') THEN '78'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:00') THEN '80'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:15') THEN '82'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:30') THEN '84'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:45') THEN '86'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:00') THEN '88'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:15') THEN '90'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:30') THEN '92'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:45') THEN '94'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:00') THEN '96'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:15') THEN '98'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:30') THEN '100'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:45') THEN '102'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:00') THEN '104'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:15') THEN '106'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:30') THEN '108'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:45') THEN '110'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:00') THEN '112'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:15') THEN '114'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:30') THEN '116'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:45') THEN '118'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:00') THEN '120'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:15') THEN '122'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:30') THEN '124'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:45') THEN '126'
	
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '22:00') THEN '128'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '22:00') AND CONVERT(time,GETDATE()) <= CONVERT(time, '24:00') THEN '130'
ELSE '' END

[실행결과]

현재시간이 오후 5시 11분이니까 90을 리턴합니다.

 

다음은 현재날짜시간 기준으로 년도,쿼터,월,년기준으로 일, 일, 주, 시간, 분, 초, 밀리초로 쪼개서 가져 쿼리입니다.

SELECT DATEPART("yy", GETDATE()) as Year

SELECT DATEPART("qq", GETDATE()) as Quarter

SELECT DATEPART("mm", GETDATE()) as month

SELECT DATEPART("dy", GETDATE()) as DayofYear

SELECT DATEPART("dd", GETDATE()) as Day

SELECT DATEPART("wk", GETDATE()) as Week

SELECT DATEPART("dw", GETDATE()) as Weekday

SELECT DATEPART("hh", GETDATE()) as Hour

SELECT DATEPART("mi", GETDATE()) as Minute

SELECT DATEPART("ss", GETDATE()) as Second

SELECT DATEPART("ms", GETDATE()) as Millisecond

[실행결과]


오늘은 여기까지

Related Posts

Leave a Reply

We use cookies to improve your experience on our website
error: Content is protected !!