在 sql server 查询中切换明智的日期时间检查 [英] Shift wise datetime checking in sql server query

问题描述

我在我的一个项目中遇到了一个 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 查询中切换明智的日期时间检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！