[MSSQL] 특정 구간 날짜, 시간 구하기 쿼리

 

CONVERT와 VARCHAR를 사용한 날짜와 시간 구간 처리

WHERE 1 = 1
  AND CONVERT(VARCHAR(10), regDate, 121) BETWEEN '2013-05-06' AND '2013-05-09'
  AND (
      (CONVERT(VARCHAR(8), regDate, 108) BETWEEN '18:00:00' AND '23:59:59')
      OR (CONVERT(VARCHAR(8), regDate, 108) BETWEEN '00:00:00' AND '09:00:00')
  )

 

CONVERT와 VARCHAR를 사용하여 더 복잡한 시간 구간 처리

WHERE 1 = 1
  AND (
      (CONVERT(VARCHAR(10), regDate, 121) = '2013-05-06' AND CONVERT(VARCHAR(8), regDate, 108) >= '18:00:00')
      OR (CONVERT(VARCHAR(10), regDate, 121) > '2013-05-06' AND CONVERT(VARCHAR(10), regDate, 121) < '2013-05-09')
      OR (CONVERT(VARCHAR(10), regDate, 121) = '2013-05-09' AND CONVERT(VARCHAR(8), regDate, 108) <= '09:00:00')
  )

 

CONVERT 함수를 사용하여 주어진 날짜와 시간 범위를 처리

WHERE 1 = 1
  AND regDate BETWEEN '2013-05-06 00:00:00' AND '2013-05-09 23:59:59'
  AND (
      (CONVERT(TIME, regDate) BETWEEN '18:00:00' AND '23:59:59')
      OR (CONVERT(TIME, regDate) BETWEEN '00:00:00' AND '09:00:00')
  )

 

CAST와 FORMAT을 사용하는 방법 ***

WHERE 1 = 1
  AND CAST(regDate AS DATE) BETWEEN '2013-05-06' AND '2013-05-09'
  AND (
      CAST(FORMAT(regDate, 'HH:mm:ss') AS TIME) >= '18:00:00'
      OR CAST(FORMAT(regDate, 'HH:mm:ss') AS TIME) <= '09:00:00'
  )

WHERE 1 = 1
  AND CAST(regDate AS DATE) BETWEEN '2013-05-06' AND '2013-05-09'
  AND (
      (CAST(FORMAT(regDate, 'HH:mm:ss') AS TIME) >= '18:00:00' AND CAST(FORMAT(regDate, 'HH:mm:ss') AS TIME) <= '23:59:59')
      OR (CAST(FORMAT(regDate, 'HH:mm:ss') AS TIME) >= '00:00:00' AND CAST(FORMAT(regDate, 'HH:mm:ss') AS TIME) <= '09:00:00')
  )

 

CAST와 DATEPART사용하는 방법 ***

WHERE 1 = 1
  AND CAST(regDate AS DATE) BETWEEN '2013-05-06' AND '2013-05-09'
  AND (
      (DATEPART(HOUR, regDate) >= 18 AND DATEPART(HOUR, regDate) <= 23)
      OR (DATEPART(HOUR, regDate) >= 0 AND DATEPART(HOUR, regDate) <= 9)
  )