URGENT - ORacle date format - need to convert Zulian and UTC dates to oracle dates

I have zulian dates like 10/05/21Z and 10/0000 (UTC) date time format I need to convert to oracle 9i date datatype.. Is there any way? urgent please since I have this pending for weeks could not get help from websites and oracle documentation.


Thanks a lot in advance
mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paquicubaCommented:
SQL> SELECT TO_DATE('2453328','J') FROM DUAL;

TO_DATE('
---------
18-NOV-04
0
paquicubaCommented:
Or vice versa:

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

TO_CHAR
-------
2453328
0
davidshockeyCommented:
ZULU DATE and UTC are the same.

Try this...

select to_date(substr('10/05/21Z',1,8),'RR/MM/DD') oradate
from dual
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
I don't recognize the term "zulian" date.  Apparently paquicuba didn't either, since he/she gave you the coversion for "julian" dates.

I wouild guess that davidshockey's suggestion is pretty close to what you need.  He assumes that in your example (10/05/21Z) the "10" is the year, the "05" is the month and the "21" is the day.  If that is correct, then the combination of "to_date" and "substr" that he gave you should work for the date.  If the year, month and day are in other positions of your example, then just re-arrange the positions of "RR" (for rounded year), "MM" for month, and "DD" for day to match the order they appear in your data.

What about the "10/0000" in your question?  Is that a time value?  If so, what time, 10:00am?  You should be able to use some combination of HH or HH24 for the hour, and MI, SS or SSSSS for the minutes and or seconds.
0
mahjagAuthor Commented:
Hi Davidshockey

I tried
select to_date(substr('10/05/21Z',1,8),'MMDD/RR') oradate
from dual
/
and got 05-oct-21 what does 21 mean? Also you mentiond UTC and Julian are same, then how can I convert 10/0000 to oracle date from utc?

Thanks
0
paquicubaCommented:
Our friend is talking Greenwich Mean Time (GMT) or Zulu time (Z).
Check this out:
http://www.usatoday.com/weather/zulu.htm
http://www.usatoday.com/weather/huricane/2000/atlantic/wflorenceh.htm

SQL> SELECT * FROM TAB5;

DT
--------------------
10/05/21Z
10/0000

SQL> SELECT DECODE(INSTR(DT,'Z'),0,TO_TIMESTAMP('10/0000','MM/HH24:MI:SS'),
  2  TO_TIMESTAMP(REPLACE(DT,'Z',''),'MM/DD/HH24:MI:SS')) FROM TAB5
  3  /

DECODE(INSTR(DT,'Z'),0,TO_TIMESTAMP('10/0000','MM/HH24:MI:SS'),TO_TIMESTAMP
---------------------------------------------------------------------------
05-OCT-04 09.00.00.000000000 PM
01-OCT-04 12.00.00.000000000 AM
0
Mark GeerlingsDatabase AdministratorCommented:
You asked "what does 21 mean?" in this response:
05-oct-21
to this query:

select to_date(substr('10/05/21Z',1,8),'MM/DD/RR') oradate
from dual
/

The "21" is the year (in the current century, or: 2021).  The MM/DD/RR format mask for the to_date command tells Oracle which portions of your string data correspond to the year (RR) month (MM) and day (DD) for an Oracle date.  The substr is necessary to strip off the trailing "Z" before Oracle converts the rest of the string to a date.
0
davidshockeyCommented:
mahjag,

The 21 is the year.  Maybe I interpreted your date incorrectly.  Is it October 5, 2021; October 21, 2005; May 10, 2021; or May 21, 2010?  I didn't know so I just guessed.  I don't recognize the format of the other part of your question.  Is 10/0000 the time?  If so is it in the form seconds/hour&minutes or is it hour/minutes&seconds?  I can't tell just from the data you've given.  Can you provide more data so that we can determine the pattern?

UTC and Julian are definitely NOT the same.  When you said zulian in your message I had to assume that you meant zulu time (which is the same as UTC and GMT).  I assumed that because if it was a Julian date then your data would have been a number representing the days since noon on January 1, 4713 BC.


For those who are interested, here are two links that describe UTC and Julian dates.

http://www.dxing.com/utcgmt.htm

http://aa.usno.navy.mil/data/docs/JulianDate.html


0
mahjagAuthor Commented:
Thanks for all the response, apologize my spelling julian for zulu date, I dont have exprience working with oracle dates. here is what I did..

SELECT DECODE(INSTR(DT,'Z'),0,TO_TIMESTAMP('10/0000','MM/HH24:MI:SS'),
  2  TO_TIMESTAMP(REPLACE(DT,'Z',''),'MM/DD/HH24:MI:SS')) FROM TAB5

this gives me 01-oct-2003 when tried, first I dont want to hardcode 10/0000 since I have a flat file having 1000 entries of dates in utc format mm/hh24:mi:ss as 10/0000 and I cannot hardcode, secondly I have julian date format like 10/14/21Z in one flat file which is stored as char(9) in flat file, I need to convert this to oracle date and store in date datatype and the other flat file has date in UTC having 10/0000 and such like dates in format mm/hh24:mi:ss.

Questions

1. Please let me know how do I convert these 2 date formats and store them in oracle date field.

Any answers would be greatly appreciated.
0
paquicubaCommented:
Sorry for confusing you when I hardcoded the date. No, you don't have to do that, just use the column name as shown below ( I cannot use "to_timestamp" where I'm right now, so I used to_date instead for demostration purposes only: )

SQL> create table zulu( zulu_date varchar2(7) );

Table created.

Elapsed: 00:00:00.00
SQL> insert into zulu values( '10/1000' );

1 row created.

Elapsed: 00:00:00.00
SQL> insert into zulu values( '08/0600' );

1 row created.

Elapsed: 00:00:00.00
SQL> insert into zulu values( '06/0500' );

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.
Elapsed: 00:00:00.00
SQL> select to_date(zulu_date,'MM/HH24:MI:SS') from zulu;

TO_DATE(Z
---------
01-OCT-04
01-AUG-04
01-JUN-04

Elapsed: 00:00:00.00


SQL> create table zulu_2( zulu_date varchar2(9) );

Table created.

Elapsed: 00:00:00.00
SQL> insert into zulu_2 values( '10/24/21Z' );

1 row created.

Elapsed: 00:00:00.00
SQL> insert into zulu_2 values( '11/15/20Z' );

1 row created.

Elapsed: 00:00:00.00
SQL> insert into zulu_2 values( '08/02/09Z' );

1 row created.

Elapsed: 00:00:00.00
SQL> select to_date(replace(zulu_date,'Z',''),'MM/DD/HH24:MI:SS') from zulu_2;

TO_DATE(R
---------
24-OCT-04
15-NOV-04
02-AUG-04

Elapsed: 00:00:00.00
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
To mahjag:

Please be careful with the terms you use.  A date value is this format: "10/14/21Z" is *NOT* a "julian" date, at least not according to the Oracle use of the term "julian".  For example, if I try to convert the current date (sysdate) to a "julian" value, this is what I get:
SQL> select to_char(sysdate,'j') from dual;

TO_CHAR
-------
2453332

If you have characters strings in this format: "10/14/21Z", they can quite easily be converted to Oracle dates, but you have to tell us which portion of: "10/14/21Z" is the year, which portion is the month and which portion is the day.  Is that:
1. October 14, 2021
or:
2. October 21, 2014

Either way, the "Z" must be stripped off with the "substr" operator, then the rest of the string can be converted to a date, like one of these examples:
select to_date(substr('10/14/21Z',1,8),'MM/DD/YY') from dual;
or:
select to_date(substr('10/14/21Z',1,8),'MM/YY/DD') from dual;

Also please tell us what date, or time, or portion of a date/time value that "10/0000" represents.  Is that hours, minutes and seconds; or hours and seconds; or some other portion of a day or time period?
0
paquicubaCommented:
@markgeer
 Check out this site:
http://www.theweatherprediction.com/basic/ztime/

"10/0000" can also be written as 10/0Z  


0
davidshockeyCommented:
I still don't think that we know what 10/0000 represents.  If we had more examples of the data then I think we might be able to determine what it is.  My best guess is that it is HH/MMSS but the single example doesn't give much of a clue.

mahjag,

We often give examples using hardcoded values and expect the questioner to substitute their column names in the statement.  So, you would change my example to something like the following.

select to_date(substr(mycolumnname,1,8),'RR/MM/DD') oradate from mytablename

Where mytablename is the name of the table you are selecting from and mycolumnname is the name of the column that contains the dates.
0
Mark GeerlingsDatabase AdministratorCommented:
OK, that helps me (and maybe some others) understand where the "Z" comes from, but that may not do much to help mahjag convert those values to Oracle dates.

So the value "10/0000" is simply a time value meaning 10:00am GMT?  Then "substr" can be used strip off the last five characters and "HH24" can be used as the format mask to help Oracle convert the "10" to the time portion of an Oracle date.  An adjustment of some hours may be necessary if the result need to be recorded in Oracle with a local time rather than Zulu time, but that is easy to do.
0
paquicubaCommented:
I agree with you  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.