如何在SQL Server 2014中进行昨天和今天之间的交易

Question

我试图拉起昨天每小时发生的所有交易的计数。我不想今天进行交易。但是我似乎无法在此条件下正确处理代码。

到目前为止我的代码：

SELECT  CAST(TransDT as DATE) as 'TransDate' 
       ,CAST(DATEPART(Hour, TransDT) as varchar) + ':00' as 'Hour' 
       ,LaneID
       ,Direction
       ,COUNT(*) as 'Ct'
FROM Traffic_analysis
WHERE TransDT >= DATEADD (DAY, -1 , SYSDATETIME())
  AND TransDT < CAST(DATEPART(DAY, SYSDATETIME()) as int) --This is the faulty line.
GROUP BY CAST(TransDT as DATE), DATEPART(Hour, TransDT), LaneID, Direction
ORDER BY CAST(TransDT as DATE) ASC

如果我评论整个故障线路，我也会获得昨天和今天的结果。我只需要昨天的结果。

我要做的是显示一个图表，该报告显示每小时每小时的流量，每个方向。将所有NB流量在一个列中和另一列中的所有SB流量中都有更好的状态。也许联盟加入？

Answer 1

您是否尝试过？

WHERE cast(TransDT as date) > DATEADD (DAY, -2 , cast(SYSDATETIME() as date))
  AND cast(TransDT as date) <= DATEADD (DAY, -1 , cast(SYSDATETIME() as date))  

实际上，这是我认为

WHERE cast(TransDT as date) = DATEADD (DAY, -1 , cast(SYSDATETIME() as date))

Answer 2

如果我了解您的问题，则可以执行以下操作：

Where   TransDT >= Convert(Date, DateAdd(Day, -1, GetDate())
And     TransDT < Convert(Date, GetDate())

Answer 3

transdt是一个日期，但是您与int。

进行比较

尝试这样的事情：

TransDT = CAST(SYSDATETIME() AS DATE)