Date Range Issue

I've been trying to find a good way to do this and it seems every idea I come up with or find only accomplishes the desired result in one scenario. Here is my issue.

I'm designing reports with SQL Reporting Services 2000 and many of these reports need to show values for a dynamic date range such as the last full month, last full week, etc. similar to the built-in functions in Crystal Reports. To accomplish this, my first effort was to filter my results by looking only at results where a specific date parameter matched the current week or month -1 or -2 or whatever was needed. The following example would look at records for the previous month only:

SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE())

I understand this may not be the most efficient way of performing this operation, but this seemed to work at the time. If I were looking at the previous week, I would simply replace MONTH([EntryDate]) with DATEPART(week, [Entry Date]) and get the same result. The issue that I've run into is that at the very beginning of the year (first week and month particularly), this code fails. Since the functions I've used above result in an integer, then statically subtract from it, at the beginning of the year, I potentially end up with zero or negative numbers which causes unpredictable results or errors.

To address my issue, I thought I would write an IF statement which would look at the result and if it were zero or a negative number, compensate accordingly. Following is an example for the previous month:

IF (MONTH(GETDATE())-1 <= 0)
BEGIN
SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1+12 AND YEAR([Entry Date]) = YEAR(GETDATE())-1
END
ELSE
BEGIN
SELECT * FROM tbExample
WHERE MONTH([Entry Date]) = MONTH(GETDATE())-1 AND YEAR([Entry Date]) = YEAR(GETDATE())
END
RETURN

The above example simply compensates by adding 12 months back to the result and subtracting 1 year instead. This is obviously limited, but seemed alright for my application. This example does not however work for the weekly ranges. The problem with using this on a weekly basis is that there are technically 53 weeks in a year, though the last week only has a few days. I am unsure if there is a way and if so, how to calculate this to add back 52 or 53 weeks respectively depending on where the current date falls.

In researching another issue, I received a tip of a different way to pull date ranges. Although this addresses the issue of rolling back into the previous year, it would only work for the monthly reports, not the weekly. Here is the example:

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(month, -1, DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)) AND [Entry Date] < DATEADD(day, DATEDIFF(day, 0, DATEADD(day, 1-day(GETDATE()), GETDATE())), 0)

As stated previously, this works perfectly by focusing on anything between the first day of the previous month and the first day of the current month. The problem is that the 1-day function does not allow for a weekly focus.

I believe I have explained my situation to the best of my ability. I am completely open to suggestion, whether it be along the line of things I have already tried or fresh, new ideas.

Thank You

Hi:

Could you try the following?

It seems DATAADD and DATEDIFF can do everything for datatime data.

--1.Monday for last week:

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate())-1, 0)

--2. For day 1 for last month SELECT DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)

|||

So if I understand correctly, you are determining the difference in weeks/months between week/month 0 and the current week/month, then subtracting 1 and then adding that number of weeks/months back to week/month 0 as the starting point?

In this case, I believe my query would look like this ...

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(week, DATEDIFF(week, 0, GETDATE())-1, 0) AND [Entry Date] < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)

... or for the monthly report ...

SELECT * FROM tbExample
WHERE [Entry Date] >= DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0) AND [Entry Date] < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

Would that be correct? I will need to do some live testing to see if this fits all of my scenarios, but this seems promising.

|||

I just show you what the function you may need. let's see what you can get from your test. Good luck.

|||

This seems to work well enough. It is somewhat odd to me that by using your method, I show only results of the last week from Monday to Sunday, but with Crystal Reports and with the other method I initially used for this task, it was looking at the last week from Sunday to Saturday. This will not impact my results enough to make a difference, but it is worth noting.

Thank You

|||

Hi,

This one is for previous Sunday: ("For servers using US English as their default language, the first day of the week is Sunday"

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate())-1, -1)

Thanks for pointing this out.