CODEWITHSUNDEEP
sqlsql-serverdatetimeif-statementcase
Lacrymae
Lacrymae

Reputation: 157

SQL compare datetime column with specific hour and minute time range

I need write a CASE statement like below;

Datetime column: '2020-04-09 08:34:00.000'

I don't care year,month,day parts. Just want to compare hour and minutes part with specific hour and minute.

If hh:mm parts of datetime value between '05:30' and '15:30' Then 1

If hh:mm parts of datetime value between '15:31' and '05:29' Then 2

How can i achieve this?

Upvotes: 0

Views: 1535

Answers (4)

Esat Erkec
Esat Erkec

Reputation: 1724

The following usage will return the time and hour

  SELECT CONVERT(VARCHAR(5),getdate(),108)  AS HourMinute

+------------+
| HourMinute |
+------------+
| 12:33      |
+------------+

So, you can use the following CASE implementation:

EDITED:

   SELECT
CONVERT(VARCHAR(5),YourDateTime,108) ,
   CASE   
      WHEN CONVERT(time,YourDateTime,108)  >=  '05:30' AND  CONVERT(time,YourDateTime,108) < '15:30' THEN 1   
       WHEN CONVERT(time,YourDateTime,108)  >=  '15:30' OR  CONVERT(time,YourDateTime,108) < '05:30' THEN 2   
   END CaseResult
FROM YourTable

Upvotes: 1

Lacrymae
Lacrymae

Reputation: 157

I solved myself i think;

DECLARE @StartDate DATETIME

SET @StartDate = '2020-04-09 16:30:00.000'

Select CASE WHEN CAST(CAST(DATEPART(HH,@StartDate)as varchar)+':'+CAST(DATEPART(mi,@StartDate)as varchar)+':'+CAST(DATEPART(ss,@StartDate) as varchar) as time(0)) between CAST('05:30:00' AS time(0)) and CAST('15:29:59' AS time(0)) THEN 1 ELSE 2 END

Thanks for all the answers.

Upvotes: 0

Pyra
Pyra

Reputation: 29

You can try something like this. 

SELECT
   CASE   
      WHEN CONVERT(TIME, YourDateTime) >= '05:30:00' AND CONVERT(TIME, YourDateTime) < '15:30:00' THEN 1   
      WHEN CONVERT(TIME, YourDateTime) >= '15:30:00' OR CONVERT(TIME, YourDateTime) < '05:30:00' THEN 2      
   END ColumnName
FROM XYZ_TABLE

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Just a simple query as

select *, case when v >= '08:30' and v <= '15:30' then 1 else 2 end
from
(
  values
  (cast('2020-04-09 08:34:00' as time))
) t(v)

Upvotes: 0

Related Questions