SQL: How to display records for only 30 days

Question

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

Answer 1

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

Answer 2

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())

Answer 3

Thanks all for the great guidance.

Greatly appreciated!

It seem that all the codes are valid, but I'm not sure why the SQL shows undefined function 'getdate'.

btw, I'm using Frontpage to do the database management.

Error:

Server error: Unable to retrieve schema information from the query:

SELECT * from results WHERE Date between dateadd(dd, -30, getdate()) and getdate()

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/submission_form.mdb.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'getdate' in expression.

Source: Microsoft OLE DB Provider for ODBC Drivers Number: -2147467259 (0x80004005)

Answer 4

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

Answer 5

Oh yes! Mixed up with the type of coding.

It works now, but unable to view the records. Hmm

Thanks Ron and All

Answer 6

Description: No value given for one or more required parameters. Number: -2147217904 (0x80040E10) Source: Microsoft JET Database Engine

SELECT * FROM Results WHERE Datesubmit between dateadd(dd,-30,DATE()) and DATE()

I'm getting the above error message.

Am I missing a syntax? e.g. comma

Need your expertise advice.

Answer 7

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.

Answer 8

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

Answer 9

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).

--
David W. Fenton
David Fenton Associates