让用户选择他想要的数据天数 [英] Let the user select how many days of data he wants

问题描述

我想仅显示当前天数据。目前我的数据是UTC时间，我编写查询的方式看起来不是显示我当前的日期数据。相反，它向我展示了包含昨天数据的数据。我已经声明@Days，用户将在其中设置他们想要数据的天数。就像今天是第10个，现在是下午5点，我希望数据从午夜到下午5点只有10日。但是当我设置@Days = -1时，它实际上会返回并向我显示从10日9点到5点的下午5点的数据。但我希望如果用户选择@Days = -7，它将仅显示最后七天的数据（即从10日午夜到下午5点），而不是从现在开始的7 * 24 = 168小时。



我尝试过：

I want to display current days data only. Currently my data is in UTC time and the way I wrote my query looks like it's not show my current days data. Instead it's showing me data which include yesterday's data too. I have declared @Days where the user will set the number of days they want the data from. Like if today is the the 10th and is currently 5pm, I want the data from midnight till 5pm only for the 10th. But when I set @Days = -1, it's actually going back and showing me data from 5pm on the 9th to 5pm of the 10th. But I want if the user selects @Days = -7, it'll show data for the last SEVEN DAYS ONLY(i.e. from 4th midnight to 5pm of the 10th) not last 7 * 24 = 168 hours from now.

What I have tried:

declare @Days int

set @Days = -1

select      

dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) as [Time]      

from  [Employee]

where dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) >= DATEADD(day, @Days , GETDATE()))

推荐答案

where dateadd(hour,datepart(hour,Timestamp),cast(CAST((Timestamp) as date) as datetime)) >= DATEADD(D, 0, DATEDIFF(D, 0, DATEADD(day, @Days + 1, GETDATE()))))

这将从您比较的日期中删除小时部分，以便您的比较变为 > = startofday



换句话说：

this will remove the hours portion from the date you are comparing against, so that your comparision becomes where employeedate >= startofday

In other words:

DATEADD(D, 0, DATEDIFF(D, 0, @sometimestamp))

选择开始@sometimestamp的日期

selects the start of day for @sometimestamp

这篇关于让用户选择他想要的数据天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！