Show Records Added Last Month

Hi guys,

I am working on my website and on the first page I would like to show CDs that were added within last month. I have DateAdded field in my CDs table. Data type is DATE. I am using SQL Server 2008.

Thank you.
LVL 7
Yury MerezhkovDevelopment Team LeadAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Try this for Access:



WHERE DateAdded >= DATESERIAL(YEAR(NOW()), MONTH(NOW())-1, 1)
AND DateAdded < DATESERIAL(YEAR(NOW()), MONTH(NOW()), 1)

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
WHERE DateAdded < CONVERT(datetime, CONVERT(varchar(8), getdate(), 120) + '01', 120)
  AND DateAdded >= DATEADD(month, -1,  CONVERT(datetime, CONVERT(varchar(8), getdate(), 120) + '01', 120))

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Or like this:
WHERE DateAdded >= DATEADD(month, -1, DATEDIFF(day, 0, GETDATE()-DAY(GETDATE())+1))
AND DateAdded < DATEADD(month, 0, DATEDIFF(day, 0, GETDATE()-DAY(GETDATE())+1))

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Anthony PerkinsCommented:
Or:
WHERE DateAdded BETWEEN DATEADD(month, -1, GETDATE() - 1) AND GETDATE()
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to clarify the difference:

 acperkins's code returns the date between now - 1 month and now, which includes the time:
  2009-04-05 07:10 to   2009-05-05 07:10 (bith inclusive)

 mvisa's and mine return the data of last full month.
  2009-04-01 00:00 (inclusive) to 2009-05-01 00:00  (exclusive)

0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
Ok thank you guys. I will try these tomorrow.
0
 
Anthony PerkinsCommented:
>>acperkins's code returns the date between now - 1 month and now, which includes the time:<<
Close, but no cigar (you missed the -1).
If anything it should be:
2009-04-04 07:10 to   2009-05-05 07:10 (bith inclusive)

Since they are DATE and not datetime the time is not relevant.  What is important is the definition of "added within last month".  If they mean In the month of April then you are of course right.  I interpreted it as within the last month, in other words it would pick up any days (using your time zone) between 2009-04-05 and 2009-05-05.

So it is all a question of semantics...
0
 
Anthony PerkinsConnect With a Mentor Commented:
On second thoughts perhaps yor misterpretation of the results was closer to what I intended then my code was :)  In other words it should have been:
WHERE DateAdded BETWEEN DATEADD(month, -1, GETDATE()) AND GETDATE()

Which would give the results you advertised:
2009-04-05 07:10 to  2009-05-05 07:10
And would cover any date between 2009-04-06 and 2009-05-05

And no doubt the purist will point out that there could be a problem at midnight. Then of course I would have to ask them to focus on "I am working on my website and on the first page I would like to show CDs", so we are not talking about eBay here.
0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
Guys, I am so sorry but since I had a lot of trouble with SQL Server I decided to use Access 2007 instead. I tried changing GETDATE() to NOW() but I get an error "No value given for one or more required parameters" on all statements that you have provided.
0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
Thanks a lot!
0
 
Anthony PerkinsCommented:
RealSnaD,
Don't forget that in MS Access DateAdded will be defined as datetime instead of date.  So you should take care to always set it to midnight when saving or you will have to modify your query.  For example, if you are using mwvisa1 solution you could modify it as follows:
WHERE DateAdded >= DATESERIAL(YEAR(Date()), MONTH(Date())-1, 1)
AND DateAdded < DATESERIAL(YEAR(Date()), MONTH(Date()), 1)
 
0
 
Anthony PerkinsCommented:
Please ignore my last comment, I have no idea what I was thinking. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.