Re: pl/sql julian date conversion

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 13 Oct 1998 19:39:57 GMT
Message-ID: <3623abe4.10021900_at_news.siol.net>

On Tue, 13 Oct 1998 17:31:13 GMT, tobrien_at_reveregroup.com wrote:

>Here's the result I'm getting with to_date:
>
> 1* select to_date('97311','YYJJJ') from dual
>SQL> /
>ERROR:
>ORA-01810: format code appears twice
>
>no rows selected
>
>What is the proper syntax?

If you realy mean julian date, then the format mask for the to_date function is *single* 'J'.

SQL> alter session set nls_date_format = 'B.C.'

Session altered.

SQL> SELECT TO_CHAR(SYSDATE,'J') FROM DUAL; TO_CHAR(SYSDATE,'J')

---

2451100

SQL> SELECT TO_DATE('97311','J') FROM DUAL; TO_DATE('97311',

---

B.C. 04-JUN-4446 As you can see, your example will give you a date more than 4,400 years B.C.! Julian date is a number of days since the 01-JAN-4712 B.C.

However, the format mask you tried to use in your example ('YYJJJ') leeds me to think you do not realy have a date in julian format! If in your example 97311 means 311th day of the year 1997, then you want to use the following format mask:

SQL> SELECT TO_DATE('97311','YYDDD') FROM DUAL; TO_DATE('97311',

---

A.D. 07-NOV-1997 HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Oct 13 1998 - 21:39:57 CEST