SQL查询忽略日期时间的时间部分 仅筛选当日工单记录

• 原员工工单系统中StartDate、EndDate字段的时间部分固定为00:00:00.0000000，格式示例：2022-07-09 00:00:00.0000000
• 字段规则调整后：保留日期对应的具体时间值，所有时间以UTC格式写入数据库，查询时需转换为中部标准时间（Central Standard Time）
• 原有查询逻辑在字段携带具体时间后筛选失效：比如StartDate为2022-07-09 08:00:00.0000000的当日早8点工单无法被匹配；如果直接修改比较符为等号，又会错误返回所有历史日期的工单
• 核心需求：忽略日期时间字段的时间值，仅返回中部标准时间下当日对应的员工工单记录

@EmployeeID nvarchar(255)


AS
begin

declare @current_cst datetimeoffset;
set @current_cst = (SELECT getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time')


SELECT  
        dbo.WorkOrders.WorkOrderID,
        dbo.WorkOrders.WorkOrderProjectID,
        dbo.WorkOrders.WorkToBeCompleted,
        dbo.WorkOrders.Status,
        dbo.WorkOrders.StartTime,
        dbo.WorkOrders.StartDate,
        dbo.WorkOrders.EndDate,
        dbo.WorkOrders.CompletedDate,
        dbo.WorkOrders.Notes,
        dbo.WorkOrders.JobTypeID,
        dbo.WorkOrders.CreatedBy,
        dbo.WorkOrders.DateCreated,
        dbo.WorkOrders.WorkToBeCompleted,
        dbo.WorkOrderAssignedEmployee.EmployeeID,
        dbo.Projects.ProjectName,
        dbo.Customers.CustomerName,
        dbo.JobType.JobTypeDescription





FROM dbo.WorkOrders
        INNER JOIN dbo.JobType ON dbo.WorkOrders.JobTypeID = dbo.JobType.JobTypeID
        --INNER JOIN dbo.WorkOrderEmployeeTimeTracker ON dbo.WorkOrderEmployeeTimeTracker.WorkOrderID = dbo.WorkOrders.WorkOrderID
        INNER JOIN dbo.WorkOrderProjects ON dbo.WorkOrders.WorkOrderProjectID = dbo.WorkOrderProjects.WorkOrderProjectID
        INNER JOIN dbo.Projects ON dbo.WorkOrderProjects.ProjectID = dbo.Projects.ProjectID
        INNER JOIN dbo.Customers ON dbo.Customers.CustomerID = dbo.Projects.CustomerID
        INNER JOIN dbo.WorkOrderAssignedEmployee ON dbo.WorkOrders.WorkOrderID = dbo.WorkOrderAssignedEmployee.WorkOrderID
--WHERE EmployeeID = @EmployeeID
WHERE
dbo.WorkOrders.StartDate <= convert(varchar(10), @current_cst, 102) and dbo.WorkOrders.EndDate >= convert(varchar(10), @current_cst, 102) and

dbo.WorkOrderAssignedEmployee.EmployeeID = @EmployeeID

order by CONVERT(time, dbo.WorkOrders.StartDate) asc


End

原WHERE条件使用convert(varchar(10), @current_cst, 102)将当前中部时间转为yyyy.mm.dd格式的字符串做比较，隐式转换时会将字符串视为当日零点的时间值。当StartDate携带当日非零点的时间（比如早8点）时，时间值大于当日零点，无法满足<=判断条件，导致当日工单漏查。

• 提前计算中部标准时间下当日的零点时间、次日零点时间作为查询边界，避免逐行转换字段带来的性能损耗
• 先将数据库中UTC存储的StartDate、EndDate转换为中部标准时间，再做日期范围匹配，彻底规避字符串隐式转换的问题
• 保留原有排序逻辑，按工单开始时间升序排列

修复后的完整SQL：

@EmployeeID nvarchar(255)
AS
begin
    -- 计算中部标准时间下当日零点、次日零点边界
    declare @current_cst datetimeoffset;
    declare @cst_today_start datetimeoffset;
    declare @cst_tomorrow_start datetimeoffset;
    
    set @current_cst = GETUTCDATE() AT TIME ZONE 'Central Standard Time';
    set @cst_today_start = CONVERT(datetimeoffset, CONVERT(date, @current_cst));
    set @cst_tomorrow_start = DATEADD(day, 1, @cst_today_start);


    SELECT  
            dbo.WorkOrders.WorkOrderID,
            dbo.WorkOrders.WorkOrderProjectID,
            dbo.WorkOrders.WorkToBeCompleted,
            dbo.WorkOrders.Status,
            dbo.WorkOrders.StartTime,
            dbo.WorkOrders.StartDate,
            dbo.WorkOrders.EndDate,
            dbo.WorkOrders.CompletedDate,
            dbo.WorkOrders.Notes,
            dbo.WorkOrders.JobTypeID,
            dbo.WorkOrders.CreatedBy,
            dbo.WorkOrders.DateCreated,
            dbo.WorkOrders.WorkToBeCompleted,
            dbo.WorkOrderAssignedEmployee.EmployeeID,
            dbo.Projects.ProjectName,
            dbo.Customers.CustomerName,
            dbo.JobType.JobTypeDescription
    FROM dbo.WorkOrders
            INNER JOIN dbo.JobType ON dbo.WorkOrders.JobTypeID = dbo.JobType.JobTypeID
            INNER JOIN dbo.WorkOrderProjects ON dbo.WorkOrders.WorkOrderProjectID = dbo.WorkOrderProjects.WorkOrderProjectID
            INNER JOIN dbo.Projects ON dbo.WorkOrderProjects.ProjectID = dbo.Projects.ProjectID
            INNER JOIN dbo.Customers ON dbo.Customers.CustomerID = dbo.Projects.CustomerID
            INNER JOIN dbo.WorkOrderAssignedEmployee ON dbo.WorkOrders.WorkOrderID = dbo.WorkOrderAssignedEmployee.WorkOrderID
    WHERE
        -- 将UTC存储的时间转为中部标准时间后，匹配当日范围，忽略时间部分
        (dbo.WorkOrders.StartDate AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') < @cst_tomorrow_start
        AND (dbo.WorkOrders.EndDate AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') >= @cst_today_start
        AND dbo.WorkOrderAssignedEmployee.EmployeeID = @EmployeeID
    order by CONVERT(time, dbo.WorkOrders.StartDate AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') asc
End

注意：如果StartDate、EndDate字段本身是datetimeoffset类型存储的UTC时间，可以省略AT TIME ZONE 'UTC'部分，直接转换为中部标准时间即可。

内容的提问来源于stack exchange，提问作者Aakburns