?
Solved

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

Posted on 2004-11-18
15
Medium Priority
?
1,844 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:mahjag
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 12618822
SQL> SELECT TO_DATE('2453328','J') FROM DUAL;

TO_DATE('
---------
18-NOV-04
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12618833
Or vice versa:

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

TO_CHAR
-------
2453328
0
 
LVL 3

Expert Comment

by:davidshockey
ID: 12618937
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12619811
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
 

Author Comment

by:mahjag
ID: 12621110
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 12622045
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12625517
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
 
LVL 3

Expert Comment

by:davidshockey
ID: 12625940
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
 

Author Comment

by:mahjag
ID: 12640968
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
 
LVL 23

Accepted Solution

by:
paquicuba earned 375 total points
ID: 12643900
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12644222
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 12645699
@markgeer
 Check out this site:
http://www.theweatherprediction.com/basic/ztime/

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


0
 
LVL 3

Expert Comment

by:davidshockey
ID: 12645862
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12645865
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 12658641
I agree with you  
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question