How to search on a month ?

[SpookiePower]

I have a date/time field where a date look
like this 31-12-2006 24:00:00

Now I want to get all the records with august month.
I have tryed to use this SQL -

SELECT * from tabel where daReportDate(month) = 08

But it does not work.
How do I search on a month ?

 

[SpookiePower]

I found out the I should do it this way -

SELECT *
FROM TableName
WHERE Month(date_field) = 08

But if I try to take the current month, like this -

SELECT *
FROM TableName
WHERE Month(date_field) = Month(GetDate())


I get this error -

Undefined function <name> in expression

Can I not use getdate in access ??

 

[Ofer Cohen]

Hi,
Use Date() to get the current date

SELECT *
FROM TableName
WHERE Month(date_field) = Month(Date())

 

[SpookiePower]

Hi,
Use Date() to get the current date

SELECT *
FROM TableName
WHERE Month(date_field) = Month(Date())

It works. Thanks

 

[Rick Brandt]

Hi,
Use Date() to get the current date

SELECT *
FROM TableName
WHERE Month(date_field) = Month(Date())

And if you want a more efficient query don't apply criteria to an expression
but rather to the field directly. Although it takes a few more seconds to
write, this would be a better query...

SELECT *
FROM TableName
WHERE date_field >= DateSerial(Year(Date()), Month(Date()), 1)
AND date_field < DateSerial(Year(Date()), Month(Date()) + 1, 0)

The reason is that if you have an index on date_field the second query above
would be able to utilize that index. The first query would have to perform
a table scan applying Month(date_field) to every row. This would likely
make no difference on a smaller table, but if you are pulling the data over
a network the table doesn't have to be very large for this to make a
difference.