DATENAME": invalid identifier
4 Message(s) by 3 Author(s) originally posted in cfml database access
| From: emmim44 |
Date: Monday, October 22, 2007
|
Error while trying to find the difference btw 2 days excluding weekends...I am
using oracle 10g with MX 8
[Macromedia][Oracle
JDBC Driver][Oracle]ORA-00904: "DATENAME": invalid
identifier
The
error occurred in /apps/www/html/ITS_REQ/ITS_REQStat.cfm:
line 9
7 :
8 :
<body>
9 :
<CFQUERY name="c1" datasource="#FormVector#">
10 : Select
11 : (DATEDIFF(dd, ITSSEC_TO_ITSDIR, ITSSEC_TO_BA) + 1)
--------------------------------------------------------------------------------
SQL Select (DATEDIFF(dd, ITSSEC_TO_ITSDIR, ITSSEC_TO_BA) + 1)
-(DATEDIFF(wk, ITSSEC_TO_ITSDIR, ITSSEC_TO_BA) * 2) -(CASE WHEN DATENAME(dw,
StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, EndDate) =
'Saturday' THEN 1 ELSE 0 END) AS TotalWorkDays from REQ_FOR_SUPPANDSERV where
EXTRACT(YEAR FROM ITSSEC_TO_ITSDIR )='2007'
| From: paross1 |
Date: Monday, October 22, 2007
|
Oracle dosen't have a datediff()
function , at least not that I am aware of, unless they added someting new with 10g.
Phil
| From: Dan Bracuk |
Date: Monday, October 22, 2007
|
I do not know about Oracle 10, but previous versions of oracle didn't have any
of the functions you are trying to use. But if it does, it does.
Your
query is hard to follow. I suggest starting from
scratch with
select count(*)
from req_for_suppandserv
where extract (year from itssec_to_itsdir) = '2007'
That will probably
crash because of the quotes. Once you get it to work,
build it up function by function until it either crashes or you get it to work.
| From: emmim44 |
Date: Monday, October 22, 2007
|
ok..thank u all.
Next Message: query problem with date range