?
Solved

one date relative to another

Posted on 2001-06-18
12
Medium Priority
?
165 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:cath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
12 Comments
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6201758
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
 

Expert Comment

by:KeCl
ID: 6202538
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
 

Accepted Solution

by:
KeCl earned 200 total points
ID: 6202545
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Expert Comment

by:CF_Spike
ID: 6202634
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
 

Author Comment

by:cath
ID: 6202744
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6202770
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
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6202796
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
 
LVL 8

Expert Comment

by:edemcs
ID: 6202981
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
 

Expert Comment

by:KeCl
ID: 6203085
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
 

Author Comment

by:cath
ID: 6203229
just about to knock off for the day - will try your suggestions tomorrow.

many thanks,

Ian
0
 

Author Comment

by:cath
ID: 6230656
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
 

Expert Comment

by:SpideyMod
ID: 8300647
Force Accepted

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question