問題描述
我在我的一個項目中遇到了一個 sql 查詢問題.實際上，我必須通過三個班次檢查某個表中的一個 DateTime 列，即，我必須根據在相應班次中下降的 RegisteredDateTime 列來獲取記錄.我們有以下輪班時間，輪班將采用 24 小時格式
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
我的問題是我在 shift1 和 shift2 中正確獲取了記錄，但在 shift3 中沒有記錄.我正在四處走動，以解決這個問題.我正在使用以下搜索查詢來獲取所有班次中的記錄
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) & "')
以上查詢用于 Shift1，同樣我也在檢查 Shift2 和 Shift3.
The above query is used for Shift1, similarly I am checking for Shift2 and Shift3 also.
大家好，@AnandPhadke 給出的想法終于對我有用了，這是我正在使用的最終查詢
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 + "')"
推薦答案
使用這些條件:
對于 shift1
WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'07:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'12:00:00')
對于 shift2
WHERE CONVERT(Time, RegisteredDateTime) > convert(time,'12:00:00') and CONVERT(Time, RegisteredDateTime) <= convert(time,'22:00:00')
對于 shift3
WHERE CONVERT(Time,RegisteredDateTime) > convert(time,'22:00:00') and CONVERT(Time, DATEADD(DD,1,RegisteredDateTime)) <= convert(time,'06:59:59')
這篇關于在 sql server 查詢中切換明智的日期時間檢查的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！