{RESOLVED} Date Logic - Calculating a date range

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

{RESOLVED} Date Logic - Calculating a date range

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-01-23 : 10:08:21

I have a report that I need to run on 2 different date ranges.

Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE

The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)

So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007

My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007

Any help would be greatly appriciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-23 : 10:12:25

this ?


where reportdaye 
      between dateadd(month, datediff(month, 0, dateadd(day, -2, getdate())), 0)
      and     dateadd(day, -2, getdate())

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-01-23 : 10:28:47

That should work. I am actually writting this for Crystal Reports but I knew the logic would be the same.

Crystal Syntax
WHERE reportdate between date(year(currentdate-2),month(currentdate-2),1) and currentdate-2

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources