Change wise time check in sql server

Question

I have one problem with sql queries in one of my projects. Actually, I have to check one DateTime column in some table with three shifts, i.e. I need to get records based on the RegisteredDateTime column, which falls in the corresponding shifts. We have the following shift timings, the shifts will be in the format of 24 hours

Shift1 : 07:00:00-12:00:00 Shift2 : 12:00:00-22:00:00 Shift3 : 22:00:00-07:00:00 

My problem is that I am writing correctly to shift1 and shift2, but not to shift3. I am going to solve this problem. I use the following search query to retrieve records in all shifts

 SELECT RequestNumber FROM Table WHERE (CONVERT(Time, RegisteredDateTime) BETWEEN '" & Shift1.Split("-")(0) &"' AND ' " & Shift1.Split("-")(1) & "') 

The above query is used for Shift1, similarly I also check Shift2 and Shift3.

Hello everyone, finally, the idea given by @AnandPhadke worked for me, its final request that I use

  Dim StartNumber As Integer = Convert.ToInt32(Shft3Arr(1).Split(":")(0)) Dim EndShift As String = (StartNumber - 1) & ":59:59" query += "(CONVERT(Time, Complaints.RegisteredDateTime) >= '" + Shft3Arr(0) + "') OR (CONVERT (Time, DATEADD(DD, 1, Complaints.RegisteredDateTime)) <= '" + EndShift + "')" 

Answer 1

Use the following conditions:

For shift1

 WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'07:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'12:00:00') 

For shift2

 WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'12:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'22:00:00') 

For shift3

 WHERE CONVERT(Time,RegisteredDateTime) > convert(time,'22:00:00') and CONVERT(Time, DATEADD(DD,1,RegisteredDateTime)) <= convert(time,'06:59:59') 

Answer 2

The easiest way here is to make SHIFT time (pun intended)!

Your time is 7-12,12-22,22-7 * (7 * the next day)

Move them to 0-5.5-15.15.15-24, using only a little magic that will match the date range you are testing with start of the shift , right?

 SELECT RequestNumber FROM Table WHERE DateAdd(hh,-7,CONVERT(Time, RegisteredDateTime)) BETWEEN .... 

This, although the glorious logic is not SARGABLE, so we shift the range, vice versa.

 SELECT RequestNumber FROM Table WHERE CONVERT(Time, RegisteredDateTime) BETWEEN DateAdd(hh,7,@date1) AND DateAdd(hh,7,@date2) 

Answer 3

Shift 3 passes in 2 days.

Add these terms also with your request.

 AND CONVERT(Time,DATEADD(DD,1,RegisteredDateTime)) < '07:00:00' AND CONVERT(Time,RegisteredDateTime) > '07:00:00' AND DATEDIFF(DD,CONVERT(DATE,RegisteredDateTime), CONVERT(Date,DATEADD(DD,1,RegisteredDateTime))) <=1 

Strike>

This answer is based on Richard aka cyberkiwi's answer above. Full credit goes to him. Please accept his answer if this works for you.

your shift data is as follows:

 Shift1 : 07:00:00-12:00:00 Shift2 : 12:00:00-22:00:00 Shift3 : 22:00:00-07:00:00 

you just need to set @shift_start_time and @shift_end_time based on switch time

 declare @shift_start_time time ; declare @shift_end_time time; IF shift 1: select @shift_start_time ='00:00:00' select @shift_end_time ='04:59:59' IF shift 2: select @shift_start_time ='05:00:00' select @shift_end_time ='14:59:59' IF shift 3: select @shift_start_time ='15:00:00' select @shift_end_time ='23:59:59' SELECT RegisteredDateTime FROM t_shift WHERE CONVERT(Time, DateAdd(hh,-7, RegisteredDateTime)) between @shift_start_time and @shift_end_time 

Answer 4

If you do not encounter any problems restoring resutls for shift 1 and shift 2, then the results from shift 3 are only universal values ​​minus the result set from the first 2 queries. So, let's say query 1 for shift 1, and query 2 for shift 2: then your query to get the results specified for shift 3 will be as follows:

 select resuestNumber from table where requestNumber not in (query 1) and requestNumber not in(query 2)