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

From: Tony van Lingen <tony.vanlingen_at_epa.qld.gov.au>
Date: Fri, 12 Jan 2007 09:27:59 +1000
Message-ID: <45A6C7FF.3000409@epa.qld.gov.au>

Mladen,

Thanks... I was running around in circles there..

If to_char uses Julian, to_date should logically do that too: turns out the ORDER BY was added more recently and that's what causes it (not the GROUP BY). To round this up, the fix is:

16* ORDER BY to_date ( substr( to_char(hrs_start,'DD/MM, Day'), 1,5 ) , 'DD/MM' )

Cheers,
Tony

Mladen Gogala wrote:

> 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.
>   


___________________________

Disclaimer

WARNING: This e-mail (including any attachments) has originated from a Queensland Government department and may contain information that is confidential, private, or covered by legal professional privilege, and may be protected by copyright.

You may use this e-mail only if you are the person(s) it was intended to be sent to and if you use it in an authorised way. No one is allowed to use, review, alter, transmit, disclose, distribute, print or copy this e-mail without appropriate authority. If you have received this e-mail in error, please inform the sender immediately by phone or e-mail and delete this e-mail, including any copies, from your computer system network and destroy any hardcopies.

Unless otherwise stated, this e-mail represents the views of the sender and not the views of the Environmental Protection Agency.

Although this e-mail has been checked for the presence of computer viruses, the Environmental Protection Agency provides no warranty that all viruses have been detected and cleaned. Any use of this e-mail could harm your computer system. It is your responsibility to ensure that this e-mail does not contain and is not affected by computer viruses, defects or interference by third parties or replication problems (including incompatibility with your computer system).

E-mails sent to and from the Environmental Protection Agency will be electronically stored, managed and may be audited, in accordance with the law and Queensland Government Information Standards (IS31, IS38, IS40, IS41 and IS42) to the extent they are consistent with the law.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 11 2007 - 17:27:59 CST