Specifying Date format
Abdul Samed, April 03, 2003 - 5:22 am UTC
Hi Tom,
I think Vipin gets data in the format YYYYDDD i.e the year followed by number of days since that year.
So he can straightaway specify the date format YYYYDDD. It'll convert that data into a valid date.
HTH.
Regards,
Aby
April 03, 2003 - 8:04 am UTC
DOH, sometimes the obvious escapes us... of course
...
field date "YYYYDDDD",
....
does it.
DOH??
Swapnil, April 03, 2003 - 8:45 am UTC
What is DOH??
Its working now........
Vipin, April 03, 2003 - 2:58 pm UTC
I put the following in my control file and it worked fine:
fieldname POSITION(27:33) char "to_char(to_date(:fieldname,'yyyyddd'),'yyyymmdd')"
Thanks......
April 03, 2003 - 3:06 pm UTC
you are not putting a date in a number or a string are you????
dates go in dates.
numbers go in numbers.
strings go in strings.
never never never put a date in a number or a string -- someone will eventually put garbage in there and someone else will always try to to_date it so they can use the format they like for display -- or they'll want to add a day or a month or something and the garbage data will defeat them.
use a DATE.
column data type............
Vipin, April 03, 2003 - 4:05 pm UTC
I also prefer the same but i have been given an existing target table which i am not allowed to change so ......
I have one related query though.......the flat file has got a header record( which says STARTnnnnn where n is a numeric digit) and a footer record which says(ENDnnnnnnn). I have tried using a When condition to discard these 2 records but i could discard only one. Does when support OR operator ?
if i use when (1:5) != 'START' , it works but if i use
when ((1:5) != 'START' OR (1:3) != 'END') , it failed......
What am i missing ?
Thanks
April 03, 2003 - 9:07 pm UTC
you can use skip=1 to skip the first and the 1:3 != END to get rid of the footer
Working fine now.........
Vipin, April 03, 2003 - 10:56 pm UTC
Thanks for your help , Tom
convert julian to date
Sharon, February 13, 2008 - 1:02 am UTC
Hi Tom,
I have this julian number 39490.4281134259 which is generated by our system imported from Australia... This Julian Date transaction was created on 12/02/2008 10:16:29, so when converting it I was expecting to get the same date... I used this following select statement to convert this
SELECT to_date(to_char(39490),'J') FROM dual;
But this statement instead of returning that date it gives me 13/FEB/4604 BC and when I TO_CHAR the results I get
SELECT to_char(to_date(to_char(39490), 'J'),'DD/MM/YYYY')
FROM dual;
I get 13/02/4604 BC
I had set:
alter session set nls_date_format = 'DD-MM-YYYY AD';
PLEASE HELP ME GUYS ON HOW TO CONVERT THIS TO MATCH THE 12/02/2008
Thanks
February 13, 2008 - 1:01 pm UTC
I have this julian number 39490.4281134259
that is not a julian date. I have no idea what it is.
PLEASE HELP US TO HELP YOU BY DESCRIBING IN SPECIFICATION FORM what this unique sort of number you have here, that is not a julian date, represents.
Michel Cadot, February 13, 2008 - 1:43 pm UTC
RE: this julian number 39490.4281134259
Duke Ganote, February 14, 2008 - 2:24 pm UTC
February 15, 2008 - 7:48 am UTC
not sure what ubiquity has to do with the fact that this is not a julian date?
http://en.wikipedia.org/wiki/Julian_day All I wanted was "tell us what this really is, what is really means"
then, we can convert it.
source MS Excel?
John Gasch, February 15, 2008 - 3:05 pm UTC
Looks suspiciously like the source was an MS Excel spreadsheet.
Enter this number into an Excel cell, then change the cell format to a date format. This yields
"2/12/2008 10:16:29 AM"
Laughing out Loud
Jay, February 15, 2008 - 3:52 pm UTC
Damnnn... Laughing out Loud! Good catch Josh..
How did you think of inserting that weird number into excel??
hahaha...
Jay
oopss...
Jay, February 15, 2008 - 3:53 pm UTC
I mean.. John.. not Josh!!
Ubiquitous; and Excel
Duke Ganote, February 22, 2008 - 4:24 pm UTC
The number is ubiquitous because that question with that number was posted on several forums.
Interesting observation that the number came from Excel (win version, not mac). Excel help says day serial #1 is January 1, 1900, so #39448 is January 1, 2008 (39,448 days after January 1, 1900).
Thanks Josh
Sharon, May 18, 2008 - 2:06 pm UTC
Hi,
Josh thanks a lot. Sorry for thanking late, as I read ur answers just now. I had come to the same conclusion after researching with the source of the database and solved the problem.
Thanks once again.
Sharon.
ORA-01854: julian date must be between 1 and 5373484
akashlavande, March 25, 2023 - 10:06 am UTC
to_char(TO_DATE((2415020 + ACTION_DATE), 'J'),'YYYY-MM-DD') as ACTION_DATE,
*
ERROR at line 6:
ORA-01854: julian date must be between 1 and 5373484
March 27, 2023 - 1:07 pm UTC
What is ACTION_DATE?
A reader, June 19, 2024 - 2:44 pm UTC
How to convert julian date '22010' to normal date 10-jan-2022
June 21, 2024 - 9:59 am UTC
TO_DATE ( val, 'J' )
But 22010 is not a Julian date in 2022. It's in the 47th century BC!
Terminological confusion
mathguy, June 22, 2024 - 10:53 am UTC
... going back to the first answer (2003).
The date format element J is not called "Julian DATE" - it is called "Julian DAY", including in Oracle documentation of date format elements.
"Julian DATE" is an old term for "Ordinal date". The Wikipedia article on "Ordinal date", in the Nomenclature section, explains this. Some old db and spreadsheet software used the term "Julian date" for the DDD or YYYY-DDD format; this was changed to Ordinal date so as not to be confused with Julian DAY (or Julian Day Number, JDN, etc.)
In the original question, as well as the most recent one from 2024, the posters clearly use the term "Julian date" to refer to the "ordinal date" concept, where DDD is the "day of the year" (Oracle documentation). "Day of the year" is another name for "ordinal date", formerly known as "Julian date".
If this had been clarified from the outset, perhaps we wouldn't see so many follow-up questions, and answers referencing J instead of DDD. For the most recent question, the answer is probably TO_DATE(val, 'YYDDD') or perhaps TO_DATE(val, 'RRDDD').
June 24, 2024 - 5:04 am UTC
If this had been clarified from the outset
welcome to the world of asktom :-)
framing questions is a skill...and it seems to be a skill that is on the decline