one date relative to another

Hi.
I have a date field called 'expires' and I need to find out if it is within a month of now. This is as far as I've got so far:

<cfset fromdate=createdate(year(now()),month(now()),day(now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires <= #fromdate#
ORDER BY expires ASC
</CFQUERY>

but obviously I gotta change something in the fromdate. I tried  month(now(1)) and month(1()) but no joy. can anyone tell me what I'm doing wrong?

low on points, hence only 50...

ta.

Ian
cathAsked:
Who is Participating?
 
KeClConnect With a Mentor Commented:
even the best way :

<cfif month(now()) IS 12>
<cfset fromdate=createdate(year(now())+1,1,day(now()))>
<cfelse>
<cfset fromdate=createdate(year(now()),month(now())+1,day(now()))>
</cfif>
0
 
CF_SpikeCommented:
This should work.

<cfset fromdate= CreateODBCDateTime(Now())>
<cfset todata = CreateODBCDateTime(DateAdd('m',1,Now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires <= '#todate#'
AND expires >= '#fromdate#'
ORDER BY expires ASC
</CFQUERY>

Spike
0
 
KeClCommented:
even the best way :

<cfif month(now()) IS 12>
<cfset fromdate=createdate(year(now())+1,1,day(now()))>
<cfelse>
<cfset fromdate=createdate(year(now()),month(now())+1,day(now()))>
</cfif>
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
CF_SpikeCommented:
KeCl

I think you will find that adding 1 month to the date as I outlined will always work.

Adding 1 month to december gets you to January the next year.

Or did I miss something?

I just noticed that there is a typo in the code I posted though,. It should read:

<cfset fromdate= CreateODBCDateTime(Now())>
<cfset todate = CreateODBCDateTime(DateAdd('m',1,Now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires <= '#todate#'
AND expires >= '#fromdate#'
ORDER BY expires ASC
</CFQUERY>

Spike
0
 
cathAuthor Commented:
Spike.
I agree that _should_ work (the second version with the typo correction) but it throws an error:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'expires <= '{ts '2001-07-18 15:48:53'}' AND expires >= '{ts '2001-06-18 15:48:53'}''.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:51).


any ideas?

Ian
0
 
CF_SpikeCommented:
There are three things you can try.

1. Try it without single quotes around the variables in the query.
<cfset fromdate= CreateODBCDateTime(Now())>
<cfset todate = CreateODBCDateTime(DateAdd('m',1,Now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires <= #todate#
AND expires >= #fromdate#
ORDER BY expires ASC
</CFQUERY>

2. Try putting preservesingleqoutes() around the variables.

<cfset fromdate= CreateODBCDateTime(Now())>
<cfset todate = CreateODBCDateTime(DateAdd('m',1,Now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires <= '#preservesingleqoutes(todate)#'
AND expires >= '#preservesingleqoutes(fromdate)#'
ORDER BY expires ASC
</CFQUERY>

3. Try both at the same time


<cfset fromdate= CreateODBCDateTime(Now())>
<cfset todate = CreateODBCDateTime(DateAdd('m',1,Now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires <= #preservesingleqoutes(todate)#
AND expires >= #preservesingleqoutes(fromdate)#
ORDER BY expires ASC
</CFQUERY>


Spike
0
 
CF_SpikeCommented:
You might also want to try. (try it with the three options above if it doesn't work at first):


<cfset fromdate= CreateODBCDateTime(Now())>
<cfset todate = CreateODBCDateTime(DateAdd('m',1,Now()))>
<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
expires BETWEEN '#preservesingleqoutes(todate)#'
AND '#preservesingleqoutes(fromdate)#'
ORDER BY expires ASC
</CFQUERY>

Spike
0
 
edemcsCommented:
Cath,
    Simply use the Months_between Function in Oracle(if it's an Oracle Database) if not, use someone else's code.

<CFQUERY DATASOURCE="marketing" NAME="GetRenewals">
SELECT * FROM client_info
WHERE
Months_Between(expires,#fromdate#) >= 1
ORDER BY expires ASC
</CFQUERY>
This will return only rows with more than a month's difference.
0
 
KeClCommented:
Just use my version :) It's the simpliest and even doesn't matter what database do you have. No odbc oracle or smth :)No need to change your interface.
PS adding 1 month to the date thru odbc may work too. I prefer native functions.
0
 
cathAuthor Commented:
just about to knock off for the day - will try your suggestions tomorrow.

many thanks,

Ian
0
 
cathAuthor Commented:
sorry sorry sorry I know I'm being unbelieveabvly slack with this question, but a paying job has come up at short notice so my own stuff is having to take a bit of a back seat at the moment...

should be on it by the end fo this week.

apologies again

Ian
0
 
SpideyModCommented:
Force Accepted

SpideyMod
Community Support Moderator @Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.