SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

convert julian date to normal date

$sidAug 3 2012 — edited Aug 6 2012

Hi All,

I am using TO_DATE(TO_CHAR(datecolumn+1900000),'YYYYDDD') sql query to convert julian date format to normal date format

it is working fine for all the date format attributes like start date, hiredate, end date except date of birth attribute

I have a date of birth attribute for this same query is not working

Can i know the soluiton

Error report:
SQL Error: ORA-01848: day of year must be between 1 and 365 (366 for leap year)
01848. 00000 - "day of year must be between 1 and 365 (366 for leap year)"
*Cause:
*Action:

Regards
user7609

This post has been answered by theoa on Aug 6 2012

Jump to Answer

BluShadow

Perhaps just use the Julian format in your to_char/to_date instead?

SQL> select to_char(sysdate,'J') from dual;

TO_CHAR
-------
2456143

SQL> select to_date(2456143,'J') from dual;

TO_DATE(2456143,'J')
--------------------
03-AUG-2012 00:00:00

1 person found this helpful

BluShadow

And then, if you're still getting the error, the chances are that you have a corrupt date in your data.

$sid

Hey Blushadow,

I have the doubt if the value is null that means if date of birth is not mentioned then what will happen?

bcz this was the error i am getting

SELECT TO_DATE(T3.YADOB,'J') as DATE_OF BIRTH FROM TESTDTA.f060116 T3

Error report:
SQL Error: ORA-01854: julian date must be between 1 and 5373484
01854. 00000 - "julian date must be between 1 and 5373484"
*Cause: An invalid Julian date was entered.
*Action: Enter a valid Julian date between 1 and 5373484.

sb92075

user7609 wrote:
Hey Blushadow,

I have the doubt if the value is null that means if date of birth is not mentioned then what will happen?

what do you want to occur when column is NULL?

$sid

I want to left that field when it is null. If that was the problem

EdStevens

user7609 wrote:
Hey Blushadow,

I have the doubt if the value is null that means if date of birth is not mentioned then what will happen?

bcz this was the error i am getting

SELECT TO_DATE(T3.YADOB,'J') as DATE_OF BIRTH FROM TESTDTA.f060116 T3

What is the data type of t3.yadob? If it is a date, you shouldn't be applying TO_DATE to it.

DATE datatype are always stored in oracle's internal format. Use TO_CHAR to present them in whatever format you want.
Please see http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Error report:
SQL Error: ORA-01854: julian date must be between 1 and 5373484
01854. 00000 - "julian date must be between 1 and 5373484"
*Cause: An invalid Julian date was entered.
*Action: Enter a valid Julian date between 1 and 5373484.

$sid

Hey Edstevens,

t3.yadob-- this gives the value of date which is in julian format

6363

user7609 wrote:

t3.yadob-- this gives the value of date which is in julian format

So to ask the same question again

What is the data type of t3.yadob?

John Spencer

Assuming that your conversion accurately represents what is in the date columns, then I would expect to see values in the birhtdate column ranging from 1 (born Jan 1 1900) to 112216 (born today). At least one of your rows in not in that range. Assuming that the actual column is numeric, then a query like:

select <whatever>
from testdta.f060116
where yadob not between 1 and 112216

should identify those rows. A null value in the column will not generate an error since adding null to a number results in null and to_date(null, format) is always valid.

John

EdStevens

user7609 wrote:
Hey Edstevens,

t3.yadob-- this gives the value of date which is in julian format

that's not what I asked. I asked what is the DATA TYPE of the column YADOB in table T3. If it is a DATE, then the data stored therein is in oracle's own internal format. It's not stored as julian, gregorian, or Chinese year of the Rat. It is oracle's internal format. It is presented* in whatever NLS_DATE format is in effect, or use of the TO_CHAR function. Pleaese go back and read the link I already provided.

Then do this:

sql> desc ts

Copy the results and paste back here.
Until we see for ouselves what the data type of t3.yadob is, anything else is just speculation.

$sid

Hey Stevens,

I am getting following error

SELECT TO_CHAR(T3.YADOB,'J') as DATE_OF_BIRTH FROM TESTDTA.f060116 T3
Error report:
SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.
*Action: Consult your manual.

$sid

Hey John,

when i tried ur query i am only getting values with null i.e 0 no others values i am getting in result for that

theoa

Answer

user7609 wrote:
I am using TO_DATE(TO_CHAR(datecolumn+1900000),'YYYYDDD') sql query to convert julian date format to normal date format

It seems that your dates are stored as an offset (number) from 00-JAN-1900.
That is NOT a Julian date. Julian dates start at 01-JAN-4713 BC. Oracle interprets the Julian origin as 01-JAN-4712 BC, probably having to do with the non-existent year 0.

Anyway, you are adding two numbers, converting it to character and interpreting it as a date with format YYYYDDD.
That will only work for a range of 1-365. Any other number will result in error ORA-01848.

What you should do instead, is take your origin as a date (that would be 01-01-1900) and add a number of days (minus 1 since your origin is one off).

sql> select to_date('19000101','YYYYMMDD') + datecolumn_which_is_actually_a_number - 1 from dual;

But the real WTF is storing a date in a number instead of a date datatype.

Marked as Answer by $sid · Sep 27 2020

$sid

Hey theoa,

Its working now i am getting dob but i am unable to understand the explanation. How it is working can u explain it. And why my query works for other date columns which are also in julian format expect date of birth

one more thing while getting values from sql query i dont wnt to get values which are null for this i am using ' is not null ' condition for particular columns in where condition still i am getting rows with null value in sql developer

$sid

NOT GIVING EXACT VALUES

$sid

Hey theoa,

Even i dnt think so i am getting exact values for dob

when i gave dob as 22-aug-1988 i got the output without converstion as 88235
but when i am using your query i got the output as 30-JUL-41

Edited by: user7609 on Aug 6, 2012 5:44 PM

chris227

select
to_date( 88235, 'RRDDD')
from dual

D
22.08.1988

select
to_char( to_date('22-aug-1988','DD-mon-YYYY') , 'RRDDD') d
from dual

D
88235

You do have a problem with the century

select
to_char( to_date('31-12-2000','DD-MM-YYYY') , 'RRDDD') c
--, to_date( 00366 + 1900000, 'YYYYDDD') d
from dual


C
00366

select
to_char( to_date('31-12-2000','DD-MM-YYYY') , 'RRDDD') c
, to_date( 00366 + 1900000, 'YYYYDDD') d
from dual

ORA-01848: day of year must be between 1 and 365 (366 for leap year)

Because 1900 wasnt a leap year as 2000 was.

Therefore you cant simple add 190000 (you had one zero to much) to your numbers, except you are sure that they all belong to 19xx.

Edited by: chris227 on 06.08.2012 06:04

theoa

user7609 wrote:
when i gave dob as 22-aug-1988 i got the output without converstion as 88235
but when i am using your query i got the output as 30-JUL-41

88235 is NOT an offset from 01-01-1900, as you mentioned in your original post.
It is the YYDDD representation of 22-aug-1988 (the 235th day of 1988).

So in what format is your original data really?

Maybe you're starting to realize now why dates should be stored in columns of type date, and not in number or character columns.

chris227

theoa wrote:

sql> select to_date('19000101','YYYYMMDD') + datecolumn_which_is_actually_a_number - 1 from dual;

You didnt mention it but this should probably be the conversion of an excel date format.
Actually it is -2 rather than -1 because they have the erroneous 29.02.1900 in their calendar which should be the 01.03.1900.

theoa

chris227 wrote:

sql> select to_date('19000101','YYYYMMDD') + datecolumn_which_is_actually_a_number - 1 from dual;

That would explain a lot.

BTW: the invalid leapday calculation was not an error in Excel per se, but implemented for backwards compatibility with Lotus 1-2-3. ;-)

1 - 20

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 3 2012

Added on Aug 3 2012

20 comments

84,896 views

SQL & PL/SQL

convert julian date to normal date

Comments

Post Details