Re: help w/ DateDiff() please
Wonderful. And just in time for the weekend. On Fri, Jan 14, 2011 at 8:33 AM, Jay Birdsell wrote: > > > 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 > > However, this works: > > ORACLE: > > >select corr_id, CAST(dte_2dlcl - #today# as integer) as draft, > CAST( dte_final - #today# as integer) as final >from EXECCORESP000 > > > > The result: draft = 0, final = 3 which is correct as dte_2dlcl= 1/14/11 > and dte_final = 1/17/11 > > CF > > >select c.* >from EXECCORESP000 c > > > > > > 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
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 However, this works: ORACLE: select corr_id, CAST(dte_2dlcl - #today# as integer) as draft, CAST( dte_final - #today# as integer) as final from EXECCORESP000 The result: draft = 0, final = 3 which is correct as dte_2dlcl= 1/14/11 and dte_final = 1/17/11 CF select c.* from EXECCORESP000 c 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
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 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 > wrote: > >> >> >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 >> 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
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 wrote: > > >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 > 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
>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 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'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
> 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 >
~|
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
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 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::
>
>select dateDiff('d',#today#,'dte_2dlcl') as m
>from execcoresp000
>
>
> Results:: Error - "DateDiff invalid identifier"
>
> try# 2
>
>
>select dateDiff('d',#today#,'dte_2dlcl') as m
>from execcoresp000
>where m < 8
>
> Results: Error M invalid identifier
>
> try # 3
>
>select dte_2dlcl
>from execcoresp000.correspondence
>where dateDiff('d',#today#,'dte_2dlcl')< 8
>
> 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
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::
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
Results:: Error - "DateDiff invalid identifier"
try# 2
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m < 8
Results: Error M invalid identifier
try # 3
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl')< 8
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
> 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
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::
>>
>> select dateDiff('d',#today#,'dte_2dlcl') as m
>> from execcoresp000
>>
>>
>> Results:: Error - "DateDiff invalid identifier"
>>
>> try# 2
>>
>>
>> select dateDiff('d',#today#,'dte_2dlcl') as m
>> from execcoresp000
>> where m< 8
>>
>> Results: Error M invalid identifier
>>
>> try # 3
>>
>> select dte_2dlcl
>> from execcoresp000.correspondence
>> where dateDiff('d',#today#,'dte_2dlcl')< 8
>>
>> 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
> 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 wrote:
> From: Jay Birdsell
> Subject: help w/ DateDiff() please
> To: "cf-talk"
> 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::
>
>
> select dateDiff('d',#today#,'dte_2dlcl') as
> m
> from
> execcoresp000
>
>
> Results:: Error - "DateDiff invalid identifier"
>
> try# 2
>
>
>
> select dateDiff('d',#today#,'dte_2dlcl') as
> m
> from
> execcoresp000
> where m < 8
>
> Results: Error M invalid identifier
>
> try # 3
> name="final8" datasource="dev11g" >
> select
> dte_2dlcl
> from
> execcoresp000.correspondence
> where
> dateDiff('d',#today#,'dte_2dlcl')< 8
>
> 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
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::
>
> select dateDiff('d',#today#,'dte_2dlcl') as m
> from execcoresp000
>
>
> Results:: Error - "DateDiff invalid identifier"
>
> try# 2
>
>
> select dateDiff('d',#today#,'dte_2dlcl') as m
> from execcoresp000
> where m< 8
>
> Results: Error M invalid identifier
>
> try # 3
>
> select dte_2dlcl
> from execcoresp000.correspondence
> where dateDiff('d',#today#,'dte_2dlcl')< 8
>
> 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
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::
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
Results:: Error - "DateDiff invalid identifier"
try# 2
select dateDiff('d',#today#,'dte_2dlcl') as m
from execcoresp000
where m < 8
Results: Error M invalid identifier
try # 3
select dte_2dlcl
from execcoresp000.correspondence
where dateDiff('d',#today#,'dte_2dlcl')< 8
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
