Solved

one date relative to another

Posted on 2001-06-18
12
164 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 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
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

Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

689 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