如何使用where子句使用SELECT语句查询CTE

Question

我正在尝试在特定时间内和结束日期内返回所有事件，然后将数据发送到我的应用程序。

服务器位于GMT中，此应用程序的大多数用户都在EST中，因此我使用DateAdd进行了-4小时的转换。

问题是，可以说用户将'03 -23-2019'的起点和结束日期放在'03 -22-2019 9:00 pm'。<<<<<<<<<<<<<<<<<</p>

由于我在Select语句中进行的转换，日期范围之外的事件正在返回。

。

该日期为'03 -22-2019 9:00 pm'的事件实际上在该数据库中为'03 -23-2019 1:00 AM'。

到目前为止

WITH incidents AS (
SELECT dv_u_store as LoginID, convert(varchar(30),dateadd(hh,-4,sys_created_on),22) as Submit_Date,
COALESCE(number,'') as Incident_Number, dv_incident_state as [Status], '' as  Product_Name, 
dv_priority as Priority, dv_short_description as [Summary]
From dbo.incident
WHERE dv_u_store IN ('004188') AND sys_created_on >= '04-23-2019' AND sys_created_on < DATEADD(DAY, 1, '04-26-2019')
)
SELECT *
FROM incidents
WHERE submit_date >= '04-23-2019' AND submit_date < DATEADD(DAY, 1, '04-26-2019')
ORDER BY submit_date DESC

开始日期： 04-23-2019

结束日期： 04-26-2019 1天(04-27-2019(

因此，在这种情况下，仅CTE返回4行：

04/25/19 1:40 AM

04/24/19 4:57 PM

04/23/19 1:40 AM

04/22/19 8:18 PM

i然后有另一个选择语句引用该CTE，该语句的Where子句再次通过开始和结束日期。

事实是，Select语句没有过滤'04 -22-19'的最后一个条目，该条目不在开始日期和结束日期范围内。

我如何查询一个开始和结束日期的CTE，以过滤出4小时转换后不再符合条件的任何内容？

以便它仅返回：

04/25/19 1:40 AM

04/24/19 4:57 PM

04/23/19 1:40 AM

我也愿意进行更好的时间转换。我只需要每个事件的时间都在EST而不是GMT，而不是GMT返回我的应用程序。

编辑

WITH incidents AS (
SELECT dv_u_store as LoginID, convert(VARCHAR(30), dateadd(hh,-4,sys_created_on), 22) as Submit_Date,
COALESCE(number,'') as Incident_Number, dv_incident_state as [Status], '' as  Product_Name, 
dv_priority as Priority, dv_short_description as [Summary]
From dbo.incident
WHERE dv_u_store IN ('004188') AND sys_created_on >= CAST('04-23-2019' AS datetime) AND sys_created_on < cast(DATEADD(DAY, 1, '04-26-2019') AS datetime)
)
SELECT *
FROM Incidents
WHERE submit_date >= cast('04-23-2019' AS datetime) AND submit_date < cast(DATEADD(DAY, 1, '04-26-2019') AS DATETIME)
ORDER BY submit_date DESC

正如评论所建议的那样，我更改了周围的转换。现在，在该子句中，我将这些字符串作为日期时间。

Answer 1

将其用于旋转...

DECLARE
    @begdate DATETIME = '2019-04-23',
    @enddate DATETIME = '2019-04-26';
SELECT 
    @begdate = DATEADD(HOUR, -4, @begdate),
    @enddate = DATEADD(HOUR, 20, @enddate); -- rather than adding a day and adjusting for UTC, just add 20 hours...
SELECT
    LoginID = i.dv_u_store,
    Submit_Date = CONVERT(VARCHAR (30), DATEADD(hh, -4, i.sys_created_on), 22),
    Incident_Number = COALESCE(i.number, ''),
    Status = i.dv_incident_state,
    Product_Name = '',
    Priority = i.dv_priority,
    Summary = i.dv_short_description
FROM
    dbo.incident i
WHERE
    i.dv_u_store IN ('004188')
    AND i.sys_created_on >= @begdate
    AND i.sys_created_on < @enddate;