Problem query for records for previous month

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

SQL Server 2005 Forums

Transact-SQL (2005)

Problem query for records for previous month

Author

Topic

SilentCodingOne
Starting Member

20 Posts

Posted - 2008-05-23 : 22:14:34

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 00:50:20

Change like this and try:-

SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN DATEADD(dd,(-1)*(DATEPART(dd,GETDATE())+1),DATEADD(mm,-1,GETDATE()))

EDIT: Missed a closing braces

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources