Solved

Show Records Added Last Month

Posted on 2009-05-04
12
385 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:RealSnaD
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 24299552
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24299578
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24301496
Or:
WHERE DateAdded BETWEEN DATEADD(month, -1, GETDATE() - 1) AND GETDATE()
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24301696
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24301706
Ok thank you guys. I will try these tomorrow.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24301737
>>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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24301766
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24305940
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 300 total points
ID: 24306693
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
 
LVL 7

Author Closing Comment

by:RealSnaD
ID: 31577777
Thanks a lot!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24307032
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24307058
Please ignore my last comment, I have no idea what I was thinking. :)
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
age from date of birth 4 42
One field not displaying content (ASP Classic) 8 25
Pass asp session variable to a js function in an asp script 6 20
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question