Problem query for records for previous month

Question

Post reply

Problem query for records for previous month

silentfreelancer

SSC Rookie

Points: 29
More actions
May 22, 2008 at 11:10 pm

#191490

I'm working on project for school that involves building a query in a video store database. The query is suppose to pull the total number of movies rented the previous month. I can get it to work if I physically put in the dates. However, part of the requirements is to set it up so the date range is auto calculated. The following is the code I have
SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN (YEAR(getdate()), MONTH(getdate()), 1)
AND (YEAR(getdate()), MONTH(getdate())+1, 0)
I get the following error message when I try to run it:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Anyone have an idea where my mistake is within the date range

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

yaron SSC-Addicted Points: 462 More actions · Answer 1

hi,

you should use this

SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory

WHERE TransactionDate BETWEEN DATEADD(M, -1, getdate()) and getdate()

Year and Month return integer and putting them with comma between

will result an invalid argument

Yours

Yaron.

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 2

If you want the data automatically from the previous month based on today's date, do NOT use BETWEEN because it will either miss most of the last day of the month or include part of the first day of the next month. This is because the DATETIME datatype also stored TIME.

For example, if you tell it to look for...

WHERE TransactionDate BETWEEN firstday_of_last_month AND lastday_of_last_month

... you will miss all but the first 1.5 milliseconds of the last day of the month. For example, 12 noon on the last day will not be included.

That being said, here's how to find the first day of last month based on the current date/time...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)

Basically, the DATEDIFF calculates the number of months since the "zero" date (which is 1900-01-01) and subtracts 1 month. Then, it adds that number of months back to the "zero" date which gives the first of the previous month at midnight.

To find the first of the current month, use the same formula without the "-1". It works the same way...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()) ,0)

Now, to include all of the dates and times for the previous month, you need to include everything from the first of the previous month (first formula), up to a NOT including the very instant that the current month started (second formula).

The proper way to do THAT is as follows...

SELECT COUNT(*) AS TotalRentalsForMonth

FROM dbo.RentalHistory

WHERE TransactionDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) -- >= First of previous month

AND TransactionDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) ,0) -- < First of current month

Lemme know if that helps...

p.s. Yaron's code will give you the last 28/29/30/31 days depending on which month it currently is... it won't give you the previous calendar month. It depends on what you are looking for...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)