DATENAME": invalid identifier

Report · Oct 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'

Report · Oct 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

Report · Oct 22, 2007

I don't know about Oracle 10, but previous versions of oracle did not 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.

Report · Oct 22, 2007

ok..thank u all.