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 have to get the records based on RegisteredDateTime column which is falling in respective shifts. We have the following shift timings, the shifts will be in 24 hr formats
Shift1 : 07:00:00-12:00:00
Shift2 : 12:00:00-22:00:00
Shift3 : 22:00:00-07:00:00
My problem is I am getting the records correctly in shift1 and shift2, but not the records lies in shift3. I am going rounds, to solve this. I am using the following search query to fetch the records in all the 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 am checking for Shift2 and Shift3 also.
Hello every one, finally the idea given by @ AnandPhadke worked for me, its the final query i am using
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 + "')"
The easiest thing to do here is to SHIFT the times (pun intended)!
Your times are 7-12,12-22,22-7* (7* being next day)
Shift them to 0-5,5-15,15-24 using just a little bit of magic, which will match the date range you are testing against being the start of the shift
, correct?
SELECT RequestNumber
FROM Table
WHERE DateAdd(hh,-7,CONVERT(Time, RegisteredDateTime))
BETWEEN ....
That, while nice in logic is not SARGABLE, so we shift the range instead, the opposite way.
SELECT RequestNumber
FROM Table
WHERE CONVERT(Time, RegisteredDateTime)
BETWEEN DateAdd(hh,7,@date1) AND DateAdd(hh,7,@date2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With