Oracle Database Discussions

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

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

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';

You need to differentiate between how the data is stored versus how the data is presented.
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()

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...

It does not work either...

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

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

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

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

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.

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 --

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

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.

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.

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