Solved

one date relative to another

Posted on 2001-06-18
12
163 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
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Suggested Solutions

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 …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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