Re: Date Compare Help
Just change that "EQ" to "LT" and you'll be set.
On Sunday, November 29, 2009, m...@markleder.com m...@markleder.com
wrote:
>
> Hi all,
> Trying to do a date compare on a file datelastmodified (retrieved through
> CFDirectory).
> If the file datelastmodified is older than 1 hour previous to Now, I want to
> refetch the file.
>
> In other words, if the file is posted at 9:00 AM, if it's presently 10:05 AM,
> I want to get a new file.
>
> Here's my attempt, but I can't get it to work right:
>
> DateCompare(dirname.DateLastModified, DateAdd("h", -1, Now())) EQ 0
>
> Thanks!
>
> Mark
>
>
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328743
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date compare
> Thanks Rich. Tried to implement but recevd errors with Convert. Where does > this command go pls? In the where? Tried that but still got errors. As Janet noted, that syntax is for MSSQL. I did a quick test in Access, and you can get similar results using the following: WHERE Format(myDate, 'mm/dd/') = '03/26/2005'; HTH, Rich Kroll > ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273766 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date compare
> Thanks Rich. Tried to implement but recevd errors with Convert. Where
> does this command go pls? In the where? Tried that but still got
> errors.
Rich noted the example uses MSSQL syntax. I don't know if Access has the
convert() function. I'm kinda' rusty but try one of these two
WHERE dateDiff('d', expiration, date()) = 7
WHERE expiration >= dateAdd('d', -7, date()) AND
expiration < dateAdd('d', -6, date())
~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 &
Flex 2
Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273763
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date compare
Thanks Rich. Tried to implement but recevd errors with Convert. Where does this command go pls? In the where? Tried that but still got errors. -Original Message- From: Richard Kroll [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 11:33 AM To: CF-Talk Subject:RE: date compare Firstly, what you have to keep in mind is that you said you have "date/time" fields. When you are trying: WHERE myDate = '03/26/2007' what you are really saying is: WHERE myDate = '2007-03-26 00:00:00.000' This comparison will fail on a record such as '2007-03-26 17:56:22.000'. To get around that you can convert the date/time field to get it to drop the time: CONVERT(char(8), myDate, 1) will give you '03/26/07' > > Select * from trials_info > > > > > Select * from trials_info > Where '#DateFormat(CreateODBCDate(now()), "mm/dd/")#' = > '#DateFormat(CreateODBCDate(sevendaysback), "mm/dd/")#' > I get from your code that you are trying to find all records that have an expiration column equal to 7 days ago. You could do this all in one query like the following (MSSQL syntax): SELECT * FROMtrials_info WHERE CONVERT(char(8), dateadd(dd, -7, expiration), 1) = CONVERT(CHAR(8), getDate(), 1) HTH, Rich Kroll ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273757 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date compare
OMG I never thought about that and it totally makes sense... Rich, thanks... that will help out in so many instances... ~Terry -Original Message- From: Richard Kroll [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 10:34 To: CF-Talk Subject: RE: date compare > #Fix( your_date )# will convert the date to a float, and then cut off > the hour/min/seconds... Then the CFQueryParam tag will convert the float > back into a date/time object. I guess you learn something every day! Nice tip Ben Rich Kroll ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273733 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date compare
> #Fix( your_date )# will convert the date to a float, and then cut off > the hour/min/seconds... Then the CFQueryParam tag will convert the float > back into a date/time object. I guess you learn something every day! Nice tip Ben Rich Kroll ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273729 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: date compare
Firstly, what you have to keep in mind is that you said you have "date/time" fields. When you are trying: WHERE myDate = '03/26/2007' what you are really saying is: WHERE myDate = '2007-03-26 00:00:00.000' This comparison will fail on a record such as '2007-03-26 17:56:22.000'. To get around that you can convert the date/time field to get it to drop the time: CONVERT(char(8), myDate, 1) will give you '03/26/07' > > Select * from trials_info > > > > > Select * from trials_info > Where '#DateFormat(CreateODBCDate(now()), "mm/dd/")#' = > '#DateFormat(CreateODBCDate(sevendaysback), "mm/dd/")#' > I get from your code that you are trying to find all records that have an expiration column equal to 7 days ago. You could do this all in one query like the following (MSSQL syntax): SELECT * FROMtrials_info WHERE CONVERT(char(8), dateadd(dd, -7, expiration), 1) = CONVERT(CHAR(8), getDate(), 1) HTH, Rich Kroll ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273728 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: date compare
Just FIX the date: #Fix( your_date )# will convert the date to a float, and then cut off the hour/min/seconds... Then the CFQueryParam tag will convert the float back into a date/time object. This will give you todays date with NO time. .. Ben Nadel Certified Advanced ColdFusion MX7 Developer www.bennadel.com Need ColdFusion Help? www.bennadel.com/ask-ben/ -Original Message- From: Orlini, Robert [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 11:04 AM To: CF-Talk Subject: RE: date compare Thanks. Is there a CF command for that? Is it createobdc? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273722 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: date compare
On Monday 26 Mar 2007, Orlini, Robert wrote: > Is there a CF command for that? Is it createobdc? DateAdd() ? -- Tom Chiverton Helping to paradigmatically expedite real-time networks on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273721 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: date compare
Thanks. Is there a CF command for that? Is it createobdc? -Original Message- From: Tom Chiverton [mailto:[EMAIL PROTECTED] Sent: Monday, March 26, 2007 10:57 AM To: CF-Talk Subject:Re: date compare On Monday 26 Mar 2007, Orlini, Robert wrote: > Would this compare work: Where '03/26/2007' = '03/26/2007'? Even though it > matches You'll need to set the hours, minutes, seconds etc. to 0. -- Tom Chiverton Helping to appropriately customize third-generation markets on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273720 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: date compare
On Monday 26 Mar 2007, Orlini, Robert wrote: > Would this compare work: Where '03/26/2007' = '03/26/2007'? Even though it > matches You'll need to set the hours, minutes, seconds etc. to 0. -- Tom Chiverton Helping to appropriately customize third-generation markets on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273718 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Date Compare in SQL?
Of course you don't need to split it into date parts anyway...
Taz
> Doesn't MySQL have the DAY function?
>
> BTW: there's no need for the cfqueryparams, and you should be using a
WHERE
> clause
>
>
> SELECT TicketID
> FROM Tickets
> WHERE MONTH(dateCreated) >= #DatePart("m", f_date)#
> AND YEAR(dateCreated) >= #DatePart("", f_date)#
> AND DAY(dateCreated) >= #DatePart("d", f_date)#
> ORDER BY TicketID
>
>
> Taz
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Date Compare in SQL?
Cedric Villat wrote:
>
> username="#request.dbuser#" password="#request.dbpass#">
> SELECT T.ticketID
> FROM Tickets T
> AND month(dateCreated) >= value="#DatePart("m", f_date)#">
> AND year(dateCreated) >= value="#DatePart("", f_date)#">
> ORDER BY T.ticketID
>
SELECTT.ticketID
FROM Tickets T
WHERE dateCreated >=
ORDER BY T.ticketID
Jochem
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Re: Date Compare in SQL?
Doesn't MySQL have the DAY function?
BTW: there's no need for the cfqueryparams, and you should be using a WHERE
clause
SELECT TicketID
FROM Tickets
WHERE MONTH(dateCreated) >= #DatePart("m", f_date)#
AND YEAR(dateCreated) >= #DatePart("", f_date)#
AND DAY(dateCreated) >= #DatePart("d", f_date)#
ORDER BY TicketID
Taz
- Original Message -
From: "Cedric Villat" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, December 24, 2002 6:00 AM
Subject: Date Compare in SQL?
> I'm trying to compare dates in my SQL statement. I can compare month and
> year, but not day. Here is my query.
>
> username="#request.dbuser#" password="#request.dbpass#">
> SELECT T.ticketID
> FROM Tickets T
> AND month(dateCreated) >= value="#DatePart("m", f_date)#">
> AND year(dateCreated) >= value="#DatePart("", f_date)#">
> ORDER BY T.ticketID
>
>
> but how can I compare the Day? I'm looking for something that will work on
> MySQL as well as MSSQL. Any ideas?
>
> Cedric
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
RE: Date Compare (SOLVED)
NEVERMIND, SOLVED!! -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 9:06 AM To: CF-Talk Subject: Date Compare I am a crunch mode here and don't have time to search for this function, my apologies. I was looking for a Javascript function the compare the current date with date field entered on the form. I thought I saw something posted here a while back that covered this topic. Any assistance would be appreciated. Thanks in advance. James __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
