Help with Filtering Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Filtering Query

Author

Topic

AlonsoQ
Starting Member

1 Post

Posted - 2012-10-18 : 11:55:00

Hey everyone...

Im having some issues with a filter I need to add to one of my querys. I need to filter a table by date, basically what I need is to filter the results depending on the hour that the query is executed, for example if I run the query after 6pm, it should only display values from 6am to 6pm of today... but if I run the report anytime between 6am and 6pm of today, it should display values from 6pm of yesterday to 6am of today...

Please help :P :D

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-18 : 12:22:19

[CODE]declare
@dtEnd datetime =
case
when cast(GetDate() as TIME) < '6:00' then DateAdd(Hour, -6, dateadd(Day, DateDiff(Day, 0, GetDate()), 0)) -- 6pm yesterday
when cast(GetDate() as TIME) < '18:00' then DateAdd(Hour, 6, dateadd(Day, DateDiff(Day, 0, GetDate()), 0)) -- 6am today
else DateAdd(Hour, 18, dateadd(Day, DateDiff(Day, 0, GetDate()), 0)) -- 6pm today
end

declare
@dtStart datetime = dateadd(Hour, -12, @dtEnd)

select
<whatever>
from
MyTable
where
MyDatetime between @dtStart and @dtEnd[/CODE]

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources