TO_DATE ISSUE IN CONVERSION

Question

I tried to execute the below query but it's throwing me error :

SELECT TO_DATE(
        TIMESTAMP '1970-01-01 00:00:00' + numtodsinterval(1511421211, 'second')
        ,'DD-MM-YYYY HH24:MI:SS') 
FROM dual

Error : ORA-01830: date format picture ends before converting entire input string

Clearly you are trying to run the code from [this recent answer on unix timestamps in Oracle](https://stackoverflow.com/a/47766750/146325) but some reason you are not running the actual code. There is no need to use `to_date()` to cast a TIMESTAMP to a DATE: Oracle just handles that for us. — APC, Dec 12 '17 at 08:07

MT0 · Answer 1 · 2017-12-12T10:54:18.393

The TO_DATE( datestring, format_model ) function takes strings as arguments.

Your query:

SELECT TO_DATE(
        TIMESTAMP '1970-01-01 00:00:00' + numtodsinterval(1511421211, 'second')
        ,'DD-MM-YYYY HH24:MI:SS'
       ) 
FROM   dual

Is passing a TIMESTAMP and a string so Oracle has to perform an implicit conversion from TIMESTAMP to a string so your function is effectively:

SELECT TO_DATE(
        TO_CHAR(
          TIMESTAMP '1970-01-01 00:00:00' + numtodsinterval(1511421211, 'second'),
          (
            SELECT value
            FROM NLS_SESSION_PARAMETERS
            WHERE parameter = 'NLS_TIMESTAMP_FORMAT'
          )
        ),
        'DD-MM-YYYY HH24:MI:SS'
       ) 
FROM   dual

If the NLS_TIMESTAMP_FORMAT session paramter does not match your format model 'DD-MM-YYYY HH24:MI:SS' then an exception will be raised.

You could change the NLS_TIMESTAMP_FORMAT parameter - but this is a session parameter that is set per user and each user can change it at any time during their session so this should NOT be the solution.

Instead, you can just use a DATE literal instead of a TIMESTAMP literal:

SELECT DATE '1970-01-01' + NUMTODSINTERVAL (1511421211, 'second')
FROM   DUAL

Or, if you want to use a timestamp then you can use the CAST function:

SELECT CAST(
         TIMESTAMP '1970-01-01 00:00:00' + NUMTODSINTERVAL (1511421211, 'second')
         AS DATE
       )
FROM   DUAL

XING · Answer 2 · 2017-12-12T09:48:00.077

0

The syntax appear somewhat incorrect to me. Try this:

SELECT   TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
       + NUMTODSINTERVAL (1511421211, 'second')
  FROM DUAL;

Edit:

As @a_horse.. said "Oracle DATE always contains a time",so if timestamp is not visible then you just need to see your NLS_DATE_FORMAT in table V$NLS_PARAMETERS. In your case its simply set to

NLS_DATE_FORMAT = 'DD-MM-YYYY';

So you need to alter the session first to get the timestamp in SQLPLUS. See below:

SQL> alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss';

Session altered.

SQL>   SELECT   TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL (15114212
11, 'second')  FROM DUAL;

TO_DATE('1970-01-01
-------------------
11-23-2017 07:13:31

edited Dec 12 '17 at 09:48

answered Dec 12 '17 at 08:07

XING

9,608
4
22
38

The OP is running code from another thread - see my comment on the question. – APC Dec 12 '17 at 08:10
1

That can be further simplified to `DATE '1970-01-01' + NUMTODSINTERVAL (1511421211, 'second')` – Dec 12 '17 at 08:10
@a_horse_with_no_name This is also correct. I didnot touch the original post of OP, just rearranged it. – XING Dec 12 '17 at 08:14
if you execute above query we get only date but not the time. – Prasad Dec 12 '17 at 08:37
I need both date and time – Prasad Dec 12 '17 at 08:38
@Prasad: an Oracle `DATE` always contains a time – Dec 12 '17 at 08:47
@ a_horse_with_no_name : but on oracle 12 i am getting only date. SQL> SELECT TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 2 + NUMTODSINTERVAL (1511421211, 'second') 3 FROM DUAL; TO_DATE(' --------- 23-NOV-17 SQL> – Prasad Dec 12 '17 at 08:55
@XING tried the updated one worked fine but when trying to compare the below where condition "unixts_to_date(evt.event_time_stamp) > trunc(sysdate) - interval '1' day;" in oracle But in mysql i have same comparison and i am getting result and the condition is "from_unixtime(evt.event_time_stamp) > date_sub(curdate(), interval 1 day) " Please help me in this conversion from mysql to oracle – Prasad Dec 12 '17 at 10:35
@Prasad First close this question if the above solution worked fine with you. Read what you need to do https://stackoverflow.com/help/someone-answers. Also open a new question with your new problem to get help. – XING Dec 12 '17 at 10:37
@Prasad: a `DATE` always contains a time in Oracle. There is no way to get a date without it. If you don't see, configure your SQL client to display the time part of a date. – Dec 12 '17 at 11:01

score 0 · Answer 3 · answered Dec 12 '17 at 08:54

0

Is this what you are expecting?

select to_char(DATE '1970-01-01' + NUMTODSINTERVAL (1511421211, 'second'), 'yyyy/mm/dd hh24:mi:ss') from dual;

answered Dec 12 '17 at 08:54

arunb2w

1,196
9
28

This is the query i am running and expecting the out put as -- select UNIX_TIMESTAMP(evt.event_time_stamp) as "---- Time -------------", from ap.ALTEREVT evt where UNIX_TIMESTAMP(evt.event_time_stamp) > trunc(sysdate) - interval '1' day; Expected output : 2017-12-11 11:50:54 2017-12-11 12:55:07 2017-12-12 10:07:04 2017-12-12 10:07:05 – Prasad Dec 12 '17 at 09:06
I believe this post can help your need.https://stackoverflow.com/questions/12105691/convert-timestamp-datatype-into-unix-timestamp-oracle – arunb2w Dec 12 '17 at 09:15
i tried this procedure and unable to get values as expected create or replace FUNCTION unix_ts_to_date( p_unix_ts IN NUMBER ) RETURN DATE IS l_date DATE; BEGIN l_date := date '1970-01-01' + p_unix_ts/60/60/24; RETURN l_date; END; – Prasad Dec 12 '17 at 09:35
@arunb2w Oracle Date has always timestamp within. There is no need to cast using to_char to show timestamp. – XING Dec 12 '17 at 09:51

TO_DATE ISSUE IN CONVERSION

3 Answers3