Re: help w/ DateDiff() please
Wait, I'm confused, are you trying to use the CF dateDiff() function, or a database's dateDiff() function? Actually Eric , I tried both. I'll try the quotes around TODAY see what that yields. ~| 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:340843 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
I suspect dte_2dlcl is the name of a date/time column in his database. Though it shouldn't be surrounded in single quotes if that's the case. On Thu, Jan 13, 2011 at 3:23 PM, Russ Michaels r...@michaels.me.uk wrote: Thank you everyone for your input. I'm using an oracle 11g db, dte_2dlcl is a date field in the table I am working with. I need to determine the interval between two dates and need to display any records with a date interval in the range of 0 - 8, I have tried both database dateDiff() and the CF version. obviously, i am getting confused with what should be surrounded by quotes. I'll try the suggestions you all have posted and get back to you. thanks again. ~| 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:340845 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
Wait. I don't think Oracle 11g even has a dateDiff function. Does it? On Fri, Jan 14, 2011 at 7:53 AM, Jay Birdsell john_birds...@hotmail.comwrote: I suspect dte_2dlcl is the name of a date/time column in his database. Though it shouldn't be surrounded in single quotes if that's the case. On Thu, Jan 13, 2011 at 3:23 PM, Russ Michaels r...@michaels.me.uk wrote: Thank you everyone for your input. I'm using an oracle 11g db, dte_2dlcl is a date field in the table I am working with. I need to determine the interval between two dates and need to display any records with a date interval in the range of 0 - 8, I have tried both database dateDiff() and the CF version. obviously, i am getting confused with what should be surrounded by quotes. I'll try the suggestions you all have posted and get back to you. thanks again. ~| 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:340846 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
Here's what Google tells me regarding days between now() and a database column value. select extract(day from (sysdate - dte_2dlcl)) I have no idea if that's correct and can't test. I think it's a step in the right direction. Here's the reference: http://stackoverflow.com/questions/1646001/how-can-i-get-the-number-of-days-between-2-dates-in-oracle-11g On Fri, Jan 14, 2011 at 8:15 AM, Michael Grant mgr...@modus.bz wrote: Wait. I don't think Oracle 11g even has a dateDiff function. Does it? On Fri, Jan 14, 2011 at 7:53 AM, Jay Birdsell john_birds...@hotmail.comwrote: I suspect dte_2dlcl is the name of a date/time column in his database. Though it shouldn't be surrounded in single quotes if that's the case. On Thu, Jan 13, 2011 at 3:23 PM, Russ Michaels r...@michaels.me.uk wrote: Thank you everyone for your input. I'm using an oracle 11g db, dte_2dlcl is a date field in the table I am working with. I need to determine the interval between two dates and need to display any records with a date interval in the range of 0 - 8, I have tried both database dateDiff() and the CF version. obviously, i am getting confused with what should be surrounded by quotes. I'll try the suggestions you all have posted and get back to you. thanks again. ~| 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:340847 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
Here's what Google tells me regarding days between now() and a
database
column value.
select extract(day from (sysdate - dte_2dlcl))
I have no idea if that's correct and can't test. I think it's a step
in the
right direction.
Here's the reference:
http://stackoverflow.
com/questions/1
646001/how-can-i-get-the-number-of-days-between-2-dates-in-oracle-11g
Michael,
select extract(day from (sysdate - dte_2dlcl)) if run in sql plus or toad but
not from with cfquery
However, this works:
ORACLE:
cfquery name=final2 datasource=dev11g
select corr_id, CAST(dte_2dlcl - #today# as integer) as draft,
CAST( dte_final - #today# as integer) as final
from EXECCORESP000
/cfquery
The result: draft = 0, final = 3 which is correct as dte_2dlcl= 1/14/11 and
dte_final = 1/17/11
CF
cfquery name=final datasource=dev11g
select c.*
from EXECCORESP000 c
/cfquery
br /
cfset today = #Now()#
cfset draft =dateDiff('d',#today#,#final.dte_final#)
This also provides the correct value.
Thanks for you input.
JB
~|
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:340848
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
Wonderful. And just in time for the weekend.
On Fri, Jan 14, 2011 at 8:33 AM, Jay Birdsell john_birds...@hotmail.comwrote:
Here's what Google tells me regarding days between now() and a
database
column value.
select extract(day from (sysdate - dte_2dlcl))
I have no idea if that's correct and can't test. I think it's a step
in the
right direction.
Here's the reference:
http://stackoverflow.
com/questions/1
646001/how-can-i-get-the-number-of-days-between-2-dates-in-oracle-11g
Michael,
select extract(day from (sysdate - dte_2dlcl)) if run in sql plus or toad
but not from with cfquery
However, this works:
ORACLE:
cfquery name=final2 datasource=dev11g
select corr_id, CAST(dte_2dlcl - #today# as integer) as draft,
CAST( dte_final - #today# as integer) as final
from EXECCORESP000
/cfquery
The result: draft = 0, final = 3 which is correct as dte_2dlcl= 1/14/11
and dte_final = 1/17/11
CF
cfquery name=final datasource=dev11g
select c.*
from EXECCORESP000 c
/cfquery
br /
cfset today = #Now()#
cfset draft =dateDiff('d',#today#,#final.dte_final#)
This also provides the correct value.
Thanks for you input.
JB
~|
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:340849
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
help w/ DateDiff() please
The dateDiff () is killing me! can someone please look at this and help me
clear my head.
What I have is a db that tracks executive correspondence when drafts are due
and when the final response is due. I need a report that will list any
correspondence that has a draft or final date within 8 days of the run date
(today). I've read that i should be able to do this right in the select
statement. However I have found a clean concise example here is what I have
tried so far;
try # 1::
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
/cfquery
Results:: Error - DateDiff invalid identifier
try# 2
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m 8
/cfquery
Results: Error M invalid identifier
try # 3
cfquery name=final8 datasource=dev11g
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl') 8
/cfquery
Results: Error - DateDiff invalid identifier
The other error i tend to get is Missing right parenthese
i really dont want to get in a bunch of nested loops to do this. any ideas?
tia,
jbird
~|
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:340770
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
Is dte_2dlcl a column in the database? You can't execute a ColdFusion
function on a database column in a query.
Thanks,
Eric Cobb
ECAR Technologies, LLC
http://www.ecartech.com
http://www.cfgears.com
On 1/13/2011 1:41 PM, Jay Birdsell wrote:
The dateDiff () is killing me! can someone please look at this and help me
clear my head.
What I have is a db that tracks executive correspondence when drafts are due
and when the final response is due. I need a report that will list any
correspondence that has a draft or final date within 8 days of the run date
(today). I've read that i should be able to do this right in the select
statement. However I have found a clean concise example here is what I have
tried so far;
try # 1::
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
/cfquery
Results:: Error - DateDiff invalid identifier
try# 2
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m 8
/cfquery
Results: Error M invalid identifier
try # 3
cfquery name=final8 datasource=dev11g
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl') 8
/cfquery
Results: Error - DateDiff invalid identifier
The other error i tend to get is Missing right parenthese
i really dont want to get in a bunch of nested loops to do this. any ideas?
tia,
jbird
~|
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:340771
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
try # 1::
select dateDiff('d',#today#,'dte_2dlcl') as
Results:: Error - DateDiff invalid identifier
If you are trying to use MS SQL's function, it does not allow quotes around the
datepart. So it would just be: d not 'd' (in quotes). Also if dte_2dlcl is
a column name, remove the quotes or your database will think it is a literal
string.
ie dateDiff('d', today, 'dte_2dlcl')
http://msdn.microsoft.com/en-us/library/ms189794.aspx
-
Leigh
http://cfsearching.blogspot.com/
--- On Thu, 1/13/11, Jay Birdsell john_birds...@hotmail.com wrote:
From: Jay Birdsell john_birds...@hotmail.com
Subject: help w/ DateDiff() please
To: cf-talk cf-talk@houseoffusion.com
Date: Thursday, January 13, 2011, 7:41 PM
The dateDiff () is killing me! can someone please
look at this and help me clear my head.
What I have is a db that tracks executive
correspondence when drafts are due and when the final
response is due. I need a report that will
list any correspondence that has a draft or final date
within 8 days of the run date (today). I've read that i
should be able to do this right in the select statement.
However I have found a clean concise example here is what I
have tried so far;
try # 1::
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as
m
from
execcoresp000
/cfquery
Results:: Error - DateDiff invalid identifier
try# 2
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as
m
from
execcoresp000
where m 8
/cfquery
Results: Error M invalid identifier
try # 3
cfquery
name=final8 datasource=dev11g
select
dte_2dlcl
from
execcoresp000.correspondence
where
dateDiff('d',#today#,'dte_2dlcl') 8
/cfquery
Results: Error - DateDiff invalid identifier
The other error i tend to get is Missing right
parenthese
i really dont want to get in a bunch of nested loops to do
this. any ideas?
tia,
jbird
~|
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:340772
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
Wait, I'm confused, are you trying to use the CF dateDiff() function, or
a database's dateDiff() function? If you're using a database's, then I
think you need to wrap #today# in quotes.
Thanks,
Eric Cobb
ECAR Technologies, LLC
http://www.ecartech.com
http://www.cfgears.com
On 1/13/2011 1:53 PM, Eric Cobb wrote:
Is dte_2dlcl a column in the database? You can't execute a ColdFusion
function on a database column in a query.
Thanks,
Eric Cobb
ECAR Technologies, LLC
http://www.ecartech.com
http://www.cfgears.com
On 1/13/2011 1:41 PM, Jay Birdsell wrote:
The dateDiff () is killing me! can someone please look at this and help me
clear my head.
What I have is a db that tracks executive correspondence when drafts are
due and when the final response is due. I need a report that will list any
correspondence that has a draft or final date within 8 days of the run date
(today). I've read that i should be able to do this right in the select
statement. However I have found a clean concise example here is what I have
tried so far;
try # 1::
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
/cfquery
Results:: Error - DateDiff invalid identifier
try# 2
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m 8
/cfquery
Results: Error M invalid identifier
try # 3
cfquery name=final8 datasource=dev11g
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl') 8
/cfquery
Results: Error - DateDiff invalid identifier
The other error i tend to get is Missing right parenthese
i really dont want to get in a bunch of nested loops to do this. any ideas?
tia,
jbird
~|
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:340773
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
a database's dateDiff() function? If you're using a database's, then I think you need to wrap #today# in quotes. Depends on whether it is a date string or date object. Though using cfqueryparam would negate the need for quotes. ~| 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:340776 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: help w/ DateDiff() please
Well dte_2dlcl is not a valid enddate for a start.
You also need to make sure the dates are in the proper format the database
understands.
If your using sql server
http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx
Russ
-Original Message-
From: Jay Birdsell [mailto:john_birds...@hotmail.com]
Sent: 13 January 2011 19:41
To: cf-talk
Subject: help w/ DateDiff() please
The dateDiff () is killing me! can someone please look at this and help me
clear my head.
What I have is a db that tracks executive correspondence when drafts are
due and when the final response is due. I need a report that will list any
correspondence that has a draft or final date within 8 days of the run date
(today). I've read that i should be able to do this right in the select
statement. However I have found a clean concise example here is what I have
tried so far;
try # 1::
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
/cfquery
Results:: Error - DateDiff invalid identifier
try# 2
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m 8
/cfquery
Results: Error M invalid identifier
try # 3
cfquery name=final8 datasource=dev11g
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl') 8
/cfquery
Results: Error - DateDiff invalid identifier
The other error i tend to get is Missing right parenthese
i really dont want to get in a bunch of nested loops to do this. any ideas?
tia,
jbird
~|
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:340778
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
I suspect dte_2dlcl is the name of a date/time column in his database.
Though it shouldn't be surrounded in single quotes if that's the case.
On Thu, Jan 13, 2011 at 3:23 PM, Russ Michaels r...@michaels.me.uk wrote:
Well dte_2dlcl is not a valid enddate for a start.
You also need to make sure the dates are in the proper format the database
understands.
If your using sql server
http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx
Russ
-Original Message-
From: Jay Birdsell [mailto:john_birds...@hotmail.com]
Sent: 13 January 2011 19:41
To: cf-talk
Subject: help w/ DateDiff() please
The dateDiff () is killing me! can someone please look at this and help me
clear my head.
What I have is a db that tracks executive correspondence when drafts are
due and when the final response is due. I need a report that will list
any
correspondence that has a draft or final date within 8 days of the run date
(today). I've read that i should be able to do this right in the select
statement. However I have found a clean concise example here is what I have
tried so far;
try # 1::
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
/cfquery
Results:: Error - DateDiff invalid identifier
try# 2
cfquery name=final8 datasource=dev11g
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m 8
/cfquery
Results: Error M invalid identifier
try # 3
cfquery name=final8 datasource=dev11g
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl') 8
/cfquery
Results: Error - DateDiff invalid identifier
The other error i tend to get is Missing right parenthese
i really dont want to get in a bunch of nested loops to do this. any
ideas?
tia,
jbird
~|
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:340779
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: help w/ DateDiff() please
try # 3
select dte_2dlcl from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl') 8
Keep in mind it is often more efficient to rewrite the query using a basic date
comparison. (Functions can sometimes impede the database's use of indexes.) The
syntax depends what you mean by within 8 days and whether your column stores
a date only or a date and time. Some examples are:
ie
- WHERE SomeDateColumn cfqueryparam value=#someDate#
cfsqltype=cf_sql_timestamp
~|
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:340780
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
