Solved

one date relative to another

Posted on 2001-06-18
12
161 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
  • 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 50 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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