Skip to Main Content

Oracle Database Discussions

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.

How to convert Epoch time to Datetime (YYYYMMDDHH24MMSS)

794703Aug 30 2010 — edited Aug 31 2010
Hi,


In our Oracle db, Datetime is stored as Epoch time (seconds since january 1st 1970). To get a readable date, I have converted those fields as followed:


TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS')

Result ex: 20100830135422


The problem is that the resulting fields are in VARCHAR2 instead of DATE so it causes issues in SAP BW reporting development. BW developpers can manage something to make it eventually work but I would like to correct this at the source if it is possible.

I have already tried to add another "TO_DATE" function at the beginning of the previous expression but I get a date in "DD-MMM-YY" format as a result even if I have forced another date format.

Any idea on how I could solve this?

Thanks a lot

Pascale

Comments

sb92075
TO_DATE(TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS'), 'YYYYMMDDHH24MISS')
mbobak
Ok, your original expression is this:
TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS')
which you correctly pointed out returns a VARCHAR2 type.

The reason it returns a VARCHAR2, is that the last function applied is a TO_CHAR(). So, if you want a DATE datatype, why not just remove the TO_CHAR(), rather than attempt to add another (pointless) conversion back to DATE?

Try this:
TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24))
-Mark
794703
I have already tried this and it provides me with a date in DD-MMM-YY format even if I have specified that I want YYYYMMDDHH24MMSS format.
Centinul
791700 wrote:
I have already tried this and it provides me with a date in DD-MMM-YY format even if I have specified that I want YYYYMMDDHH24MMSS format.
The way a DATE is stored and displayed are two separate things. If you want to display the date in that format do the following:
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDHH24MMSS';
sb92075
You need to differentiate between how the data is stored versus how the data is presented.
I have already tried this and it provides me with a date in DD-MMM-YY format
most like above is true even when you do as follows


SELECT SYSDATE FROM DUAL;

You'll get 30-AUG-10 because that is the DB's default NLS_DATE_FORMAT

If/when you want to SEE any date in a specific format, then use TO_CHAR()
794703
I have already tried this and it is not working. It gives me a date in "YY-MMM-DD" format which is not the required format.

On Oracle forum, it is promoted to convert an Epoch date to a readable date as I have previously exposed:

TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS')

My problem is how to change this TO_CHAR proposition into DATE format...
794703
It does not work either...

I get the following error message: ORA-01810 - Format code appears twice
794703
OK,


Data about date is stored in Epoch
SysDate appears as DD-MMM-YY*
I need Data about date to appear in Datetime format in queries


This been said, if I have understand you well, there is no way I can convert the result of the following to my required date format:

TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS')

Edited by: 791700 on Aug 30, 2010 12:07 PM

Edited by: 791700 on Aug 30, 2010 12:07 PM
sb92075
QL> @dates
SQL> drop TABLE EXAMPLE;

Table dropped.

SQL> CREATE TABLE EXAMPLE(NOW DATE);

Table created.

SQL> -- any valid "day" 'string' can be converted into DATE datatype using TO_DATE()
SQL> -- the content of the 'string' must match the "FORMAT" which is 2nd arguement
SQL> INSERT INTO  EXAMPLE VALUES(TO_DATE('2010-AUG-30','YYYY-MON-DD'));

1 row created.

SQL> INSERT INTO  EXAMPLE VALUES(TO_DATE('AUG-30-2010','MON-DD-YYYY'));

1 row created.

SQL> INSERT INTO  EXAMPLE VALUES(TO_DATE('30-2010-AUG','DD-YYYY-MON'));

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT * FROM EXAMPLE;

NOW
---------
30-AUG-10
30-AUG-10
30-AUG-10
mbobak
Ok, there is clearly a disconnect here.

Oracle stores dates in a DATE datatype. This means that, internally, it will store enough information for a 4 digit year, a month, date, hour in 24 hour time, minute, and second. All that information is stored in the DATE datatype inside the database, but, it's stored in an internal format, that has nothing to do with what it will look like when you print it. When you want to retrieve it, Oracle must have a date format string, so that it knows how to display that information. If you do not provide a date format string via the TO_CHAR() function, Oracle has a parameter called NLS_DATE_FORMAT that provides the default format to use. If you have not set the NLS_DATE_FORMAT, it defaults to DD-MON-YY.

So, having said all that, what are you actually trying to do?

You started with this expression:
TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS')
How is that expression used? In a SELECT statement? Then you probably want it the way it's written above. In an INSERT statement? If so, then if the datatype of the column you're inserting into is DATE, then you probably need to eliminate that outer TO_CHAR conversion, so that the expression returns a date that can be inserted into DATE column.

Tell us more about what you want to do, and perhaps we can offer further help.

-Mark
794703
Good question.

The expression is in a select statement used to construct a view on which SAP BW developper is connecting to upload his cubes.

The reason behind the need of presenting date values in YYYYMMDDHH24MMSS format is to be SAP standards compliant.

My only concern is that I would like to solve the issue at the source by changing the VARCHAR2 to a DATE format instead of modifying the format in SAP BW Developpement.
Mark D Powell
A view is just a stored qeury so it seems like your issue comes down to how the data is stored in the source table and if that table is delivered as part of the SAP product or if it is a local addition.

In the case of a local addition you should have control of the data types in use but if the table is delivered as part of SAP I would think you need to leave the definition alone and deal with the data as it is defined by SAP.

Now if the source is declared as a date datatype then you should have no issue as you can use to_char to arrange the display format any way you want. In the case of the source being a varchar2 then converting the value to a date datatype via to_date and then using to_char on that to set the dispaly format should be an option.

Note that a view can have data of data datatype in which case you need to use to_char or nls_date_format to control how the value displays or as I think what you need you can code the to_char as part of the view so the date value is formatted for the end-user.

HTH -- Mark D Powell --
mbobak
Ok.....next question:

If you want the format to be YYYYMMDDHH24MISS, and you use:
TO_CHAR(TO_DATE('19700101000000', 'YYYYMMDDHH24MISS')+((DATE_FIELDS-18000) /(60*60*24)),'YYYYMMDDHH24MISS')
Doesn't that solve your problem? It provides the format you requested.

If it does not solve the problem, then, specifically, why not? What problem does it cause? How is it deficient?

-Mark
794703
One thing I have forgotten to specify is that the Oracle database which is my source for BW reporting is Remedy database, not SAP. We have built BW reporting for Remedy ticketing application.
794703
It provides the good format but the resulting field is in VARCHAR2 instead of DATE format.
Centinul
791700 wrote:
It provides the good format but the resulting field is in VARCHAR2 instead of DATE format.
What do you mean by "DATE format"?

When you view a DATE in Oracle you see the character representation of it. You can control that either by database or session NLS settings or explicitly using the TO_CHAR function.
mbobak
You can't have it both ways. If you're displaying it, it will print in the format specified by TO_CHAR() or NLS_DATE_FORMAT. Whatever that format is, it's a string, by definition.

Why is that a problem? If you're inserting into a date datatype, then remove the outermost TO_CHAR() conversion and insert a DATE datatype into the table.

DATE datatypes are stored in an internal format. If you print a date, you must choose a format.

Hope that's clear.

If you still have a question or problem, please provide a specific example showing the result you're getting, why that result is not useful, and what you want the result to be.

-Mark
1 - 17
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 28 2010
Added on Aug 30 2010
17 comments
62,410 views