Unable to convert date and varchar to date in Oracle db

Question

I have following query:

SELECT TO_DATE(date || ' ' || time, 'DD-MON-YY HH24:MI:SS' ) FROM table;

date is DATE

time is VARCHAR2(4000 CHAR)

After executing query I get 27-OCT-16 without time instead of 27-OCT-16 14:26:09.

Is there any solution?

I'm not quite sure what the issue is but I would try the following: `SELECT TO_DATE(TO_CHAR(date, 'DD-MON-YY') || ' ' || time, 'DD-MON-YY HH24:MI:SS' ) FROM table;` — David Faber, Sep 29 '17 at 14:44
Another possibility is that Oracle is returning the date & time correctly but formatting it for output without the time part. That is, your query is working as is (but dependent on the NLS settings), but not displaying a time even though it is returned. — David Faber, Sep 29 '17 at 14:46
Are you sure that the DATE value does not contain time component already? — Wernfried Domscheit, Sep 29 '17 at 14:50
@WernfriedDomscheit yes. `date` is `27-OCT-16` and `time` is `14:26:09` — Oleksandr Stefanovskyi, Sep 29 '17 at 14:54

MT0 · Accepted Answer · 2017-09-29T15:03:03.860

[TL;DR] Your date has a time component but you are relying on implicit string conversions and the default format model (probably DD-MON-YY) does not display the time component.

Your query:

SELECT TO_DATE( date || ' ' || time, 'DD-MON-YY HH24:MI:SS' )
FROM   table;

The DATE does not have a format; it is stored internally as 7-bytes and it is the the client program which you are using to access the database which will apply its own formatting to the date (which you can usually set via the preferences in that program).

For SQL/Plus and SQL developer, this is usually the NLS_DATE_FORMAT session parameter. Which you can find the format of using the query:

SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT'

Trying to concatenate a DATE with a string will implicitly convert the date to a string and the query is equivalent to:

SELECT TO_DATE(
         TO_CHAR(
           date,
           ( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
         )
         || time,
         'DD-MON-YY HH24:MI:SS'
       )
FROM   table

And then the second implicit conversion to something the client program can display will make your query the equivalent of:

SELECT TO_CHAR(
         TO_DATE(
           TO_CHAR(
             date,
             (SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT')
           )
           || time,
           'DD-MON-YY HH24:MI:SS'
         ),
         (SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT')
       )
FROM   table

I would guess that your NLS_DATE_FORMAT is DD-MON-YY (or DD-MON-YYYY) - otherwise the initial conversion would fail and this has no time component so the UI's conversion from a date back to a string also does not include a time component.

If you want the DATE with a specific format then just use TO_CHAR to show it.

SELECT TO_CHAR(
         TO_DATE(
           TO_CHAR( date, 'DD-MON-YYYY' ) || ' ' || time,
           'DD-MON-YYYY HH24:MI:SS'
         ),
         'DD-MON-YYYY HH24:MI:SS'
       )
FROM   table;

Or you could alter the session variable (but this is likely to break your initial implicit string conversion) so, don't do that, just change all your implicit string conversions to explicit ones and specify the format model you are using.

Unable to convert date and varchar to date in Oracle db

1 Answers1