Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Julian Date error in SQL statement - but it has no Julian date

Re: Julian Date error in SQL statement - but it has no Julian date

From: Mladen Gogala <mgogala_at_verizon.net>
Date: Wed, 10 Jan 2007 23:13:09 -0500
Message-id: <1168488789l.3404l.1l@medo.noip.com>


On 01/10/2007 10:06:51 PM, Tony van Lingen wrote:
> G'day,
>
> I've just been alerted to a strange problem with a sql script that's
> been in use for years here. It contains the following statement:
>
> SQL> l
> 1 select to_char(hrs_start,'DD/MM, Day') day,
> 2 round(Sum(nvl(hrs_end,sysdate)-hrs_start)*24,2) hours
> 3 FROM ACTIVITIES a,
> 4 hours h,
> 5 activity_persons p,
> 6 personnel per
> 7 WHERE a.JOB_ID = p.ACT_JOB_ID
> 8 AND a.ID = p.ACT_ID
> 9 AND h.ACT_ID = a.ID
> 10 AND h.ACT_JOB_ID = a.JOB_ID
> 11 AND h.PRS_CODE = p.PRS_CODE
> 12 AND h.hrs_start >= to_date (nvl(:startdate, '1-1-1900') ,
> 'DD-MM-YYYY' )
> 13 AND h.hrs_end <= nvl(to_date( :enddate, 'DD-MM-YYYY' ), sysdate)
> 14 AND ((per.ORAUSER=user) AND (p.PRS_CODE=per.code))
> 15 GROUP BY to_char(hrs_start,'DD/MM, Day')
> 16* ORDER BY to_date ( to_char(hrs_start,'DD/MM, Day'), 'DD/MM, Day' )
>
> It produces a nice sorted list of the hours worked on the various days
> in the given period (between :startdate and :enddate). The strange ORDER
> BY clause is necessary because of the GROUP BY on to_char(..). It's
> always worked nicely, for current dates as well as for previous years.
> This is the expected output:
>
> SQL> exec :startdate := '1-1-2007';
> SQL> exec :enddate := '31-1-2007';
> SQL> /
>
> DAY HOURS
> ---------------- ----------
> 02/01, Tuesday 9.53
> 03/01, Wednesday 9.9
> 04/01, Thursday 7.97
> 05/01, Friday 8.52
> 08/01, Monday 8.42
> 09/01, Tuesday 8.52
> 10/01, Wednesday 8.95
> 11/01, Thursday .57
>
> In the past year we migrated from 9.2x to 10.1.0.4 and the following
> errors now show up when we try and produce a list for any previous year:
>
> SQL> exec :startdate := '1-12-2006';
> SQL> /
> GROUP BY to_char(hrs_start,'DD/MM, Day')
> *
> ERROR at line 15:
> ORA-01835: day of week conflicts with Julian date
>
>
> SQL> exec :enddate := '31-12-2006' ;
> SQL> /
> GROUP BY to_char(hrs_start,'DD/MM, Day')
> *
> ERROR at line 15:
> ORA-01835: day of week conflicts with Julian date
>
> And julian dates aren't even asked for! Has anyone seen this before?
> (Oracle DB 10.1.0.4.0 on Solaris, SQLPlus 10.1.0.4.0 on Windows & Linux)
>
> --
> Tony van Lingen
>

Tony, Here is what Metalink note 19159.1

Error: ORA 1835
Text: day of week conflicts with Julian date



Cause: A Julian date was specified with the day of the week, but the weekday
        did not correspond to the Julian date.
        If the day of the week is specified with a Julian date, it must be the 
        same day of the week as the Julian date.
Action: Remove the day of the week value from the date specification or enter 
        the correct day of the week for the Julian date.

It's an old note and I have no clue why did it hit you right now, but it apparently did hit you, nevertheless. You have Julian dates: TO_CHAR calculations internally converts the date to Julian date.

-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 10 2007 - 22:13:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US