3

I tried to use 

SELECT * from Results
WHERE DATEDIFF(d,Date,getdate())<30

But it seem having error with this.

For each record submitted will only display 30 days. May I know if my syntax is correct?

Greatly Appreciated, Stan

JohnFx
  • 34,542
  • 18
  • 104
  • 162

6 Answers6

13

Syntax looks OK, but you might need to 'quote' Date:

SELECT * from Results WHERE DATEDIFF(d, [Date], getdate()) < 30

Do you have a column called Date in Results?

BTW, that won't be able to use an index, whereas this will:

SELECT * from Results WHERE [Date] >= DATEADD(d, -30, getdate())
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
6

First off, you (and most of the replies in this thread) are mixing up SQL variants. You said nothing in your question about SQL Server, yet, you're getting recommendations on using SQL Server syntax (i.e., GetDate()).

The answer from JohnFx provides you correct Jet SQL syntax:

SELECT *  
FROM results 
WHERE ([Date] between DateAdd("d", -30, Date()) and Date())

But he is also correct that naming a field "Date" is really bad in Access/Jet. The WHERE clause might be improved with:

WHERE (results.Date between DateAdd("d", -30, Date()) and Date())

but I can't say for sure -- I would never name a field "Date" so would never encounter this kind of problem.

But there may be a simpler version, given that Jet stores its dates in a format where the integer part indicates the date and the decimal part the time. Because of that, when dealing with dates (as opposed to weeks or months or quarters), you can perform date math on them directly:

WHERE results.Date BETWEEN results.Date-30 AND Date()

This will give you exactly the same results as JohnFx's DateDiff() version, but won't need to call the DateAdd function for each row.

The key thing is using the proper syntax for a Jet database, and that means that the first argument for DateAdd() is a string value ("d") and that you can't use a SQL Server function (GetDate()), but must instead use Jet's function for the same purpose (Date()). But it's also a good idea to avoid using Jet/Access functions in your SQL when you don't have to, and that's why I believe that the "results.Date-30" version is going to be better than the DateAdd() version.

Aside: I really wish that those who post answers involving SQL would pay close attention to what database engine the questioner is using to execute the SQL. A lot of wrong answers are found in this thread precisely because those posters did not read the question carefully (it was pretty clear from the keywords what database engine was involved).

sth
  • 222,467
  • 53
  • 283
  • 367
David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • I missed the tag on the initial question which would have caused me to provide different syntax. That said what keywords clued you into knowing it was a Jet SQL syntax? – ahsteele Mar 03 '09 at 00:03
  • Hi David, Thanks for the insight and clarification. I should have given a clearer question to avoid any confusion to all advisors. I renamed and corrected the code according to Jets SQL not SQL server function. It works now! Greatly Appreciated. =) –  Mar 03 '09 at 01:45
  • "Better" is in the eye of the beholder. I find the DateAdd version kinder on the eye because the temporal granule "d" = day is more explicit. – onedaywhen Mar 03 '09 at 08:55
  • If "better" means "optimized for performance" then consider making the results.Date column the first-declared column in the table's PRIMARY KEY constraint to favour clustering. – onedaywhen Mar 03 '09 at 08:56
  • What clued me into Jet SQL was the MS Access tag and the FrontPage reference. Nobody mentioned SQL Server (the "SQL" tag is just generic for SQL problems, no?). – David-W-Fenton Mar 04 '09 at 04:51
3

Try this:

SELECT *  
FROM results 
WHERE ([Date] between DateAdd("d", -30, Date()) and Date())

One other heads-up. Naming a field "Date" in Access is generally a bad idea. It is a reserved word and you will have to use brackets [] around it in all of your queries.

JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • Oh, and I am obligated to add. Don't do "SELECT *", list the specific fields you want. The syntax of my response was just an example since I don't know whch fields you need. – JohnFx Mar 02 '09 at 17:23
2

If you are using a Microsoft Access database, the function to get the current date is Date() rather than getdate() (that's for SQL Server).

Ron Savage
  • 10,923
  • 4
  • 26
  • 35
0

Your query looks sound. What is the error that you're running in to?

Jason
  • 985
  • 1
  • 6
  • 12
0

You did not specify SQL Server as your db. In Access, the syntax for DateAdd is: DateAdd("d", 1, "31-Jan-95").

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • Strictly speaking, in *Jet* that's the syntax. Access has no SQL syntax -- it only has Jet SQL syntax. – David-W-Fenton Mar 02 '09 at 23:30
  • 'Strictly'? No, you are using the term 'Jet' *loosely* to mean 'Microsoft Jet and the Microsoft Access Engine'. Considering the word 'Access' is contained within the strict definition then it is perfectly acceptable to refer to 'Access SQL' colloquially and everyone will know what is meant. – onedaywhen Mar 03 '09 at 08:48
  • Many people do it. But it leads to an awful lot of confusion in both discussions and in the minds of many people who aren't making the distinction between their database engine (Jet) and their development platform (Access). – David-W-Fenton Mar 04 '09 at 04:53
  • The problem is people with Access application experience are wasting their time looking at Access tagged questions such as, "How do I connect a PHP website to an .mdb file?" Access has nothing to do with it. – JeffO Mar 05 '09 at 18:31