Hi,

I am trying to create a cfquery that will search the renewaldate field and
will pull up records that will have the same month and year as the current
month and year.

Any suggestions on the best way to do this?

TIA

Your date field might appear like yyyy-mm-dd, but that's a display thing. I
don't use mysql but when I googled "mysql date functions" I found this.
http://www.w3schools.com/SQL/sql_dates.asp

Year and month functions did not appear, but curdate returns the current date.
That being the case, Cold fusion functions are probably a better way to go.

Use a combination of now(), year, month, and dateadd to get the 1st day of the
current month and the first day of the next month. Let's call those variables
date1 and date2.

They get used in your query like this

where renewal_date >= <cfqueryparam value="#date1#">
and renewal_date < <cfqueryparam value="#date2#">

mSQL has a whole array of built-in date functions you can use:

SELECT ..., your_date_column
FROM members
WHERE YEAR(your_date_column) = YEAR(NOW()) AND MONTH(your_date_column) =
MONTH(NOW())

note that all the functions above are MySQL functions, NOT cf functions,
so DO NOT use # around them.


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Azadi, That worked great! Thank you

[q]Originally posted by: Newsgroup User
mSQL has a whole array of built-in date functions you can use:

SELECT ..., your_date_column
FROM members
WHERE YEAR(your_date_column) = YEAR(NOW()) AND MONTH(your_date_column) =
MONTH(NOW())

note that all the functions above are MySQL functions, NOT cf functions,
so DO NOT use # around them.


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
[/q]