Re: CF datediff to SQL datediff

2012-02-08 Thread Akos Fortagh 

thank you all for the suggestions, really helpful 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349834
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: CF datediff to SQL datediff

2012-02-08 Thread Leigh 

> where currentDate between offerStartDate and offerEndDate

Hm .. I may have misunderstood. If the goal is to retrieve *only* items that 
are on special, go with Rick's suggestion. 

 
-Leigh

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349830
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: CF datediff to SQL datediff

2012-02-08 Thread Rick Faircloth 

In MySQL you can use "between" for finding dates that are
after the first date and earlier than the second date.

If you've got the first date and second date in MySQL
columns you can use:

where currentDate between offerStartDate and offerEndDate

Try working something like that into your SQL...

Rick

-Original Message-
From: Akos Fortagh [mailto:akos.fort...@yahoo.com] 
Sent: Wednesday, February 08, 2012 11:05 AM
To: cf-talk
Subject: Re: CF datediff to SQL datediff


thanks for the reply, I'm using mySQL 



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349828
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: CF datediff to SQL datediff

2012-02-08 Thread Leigh 

MySQL does support dateDiff. But you could also use a simple date comparison 
instead. Return  one (1) if the item is on offer today, or zero (0) if it is 
not.

 
SELECT   
CASE WHEN offerStart <= curDate() AND offerEnd >= curDate() THEN 1 ELSE 0 END 
AS IsOnOffer


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349827
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: CF datediff to SQL datediff

2012-02-08 Thread Akos Fortagh 

thanks for the reply, I'm using mySQL 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349825
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

RE: CF datediff to SQL datediff

2012-02-08 Thread Rick Faircloth 

What database are you using?

-Original Message-
From: Akos Fortagh [mailto:akos.fort...@yahoo.com] 
Sent: Wednesday, February 08, 2012 9:52 AM
To: cf-talk
Subject: CF datediff to SQL datediff


Wondering if anyone might be able to help with this.
I have products in the db that have offerStart and offerEnd dates and
according to today's date they are on offer today or not, so the following
works great when individually looking at the products and CF does the work:







but now I need to output ALL products in one huge list(well I'll break them
down into pages) and show which one is on offer so I thought SQL should do
the work.  How could I 'translate' the above CF into SQL?
Any help would be greatly appreciated. 



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349824
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

CF datediff to SQL datediff

2012-02-08 Thread Akos Fortagh 

Wondering if anyone might be able to help with this.
I have products in the db that have offerStart and offerEnd dates and according 
to today's date they are on offer today or not, so the following works great 
when individually looking at the products and CF does the work:







but now I need to output ALL products in one huge list(well I'll break them 
down into pages) and show which one is on offer so I thought SQL should do the 
work.  How could I 'translate' the above CF into SQL?
Any help would be greatly appreciated. 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349820
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm