1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Convert char to_date doesn't work

Discussion in 'SQL PL/SQL' started by IlanM, Aug 31, 2017.

  1. IlanM

    IlanM Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Mexico
    Hi. I have a field called ACTUALFINISH in format DD/MM/YY and I want to add the hours and minutes.

    For example, I have this date: 29/08/17

    What I do is the next query:

    select to_char(actualfinish, 'DD/MM/YY HH24:MI') from table;

    This query works and I get: 29/08/17 17:39

    But when I try to return to Date type using this query:

    select TO_DATE( (TO_CHAR(actualfinish, 'DD/MM/YY HH24:MI') ), 'DD/MM/YY HH24:MI') from table;

    I get again the initial date: 29/08/17 and the hours and minutes are ignored.

    What I'm doing wrong?

    I'm using Oracle 11g, thanks for the help
     
  2. njuro

    njuro Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Poland
    What software are you using to connect to DB?
    If it is Oracle SQL Developer then check the option in TOOLS->Preferences->Database->NLS
    and value for Date Format
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is no problem with the conversion as it most certainly works. The problem lies in your default NLS_DATE_FORMAT as it doesn't provide for the time portion of the date value. The time portion IS there, you simply don't see it because the date mask you're using doesn't return it. Here is an example to help you:

    Code (SQL):
    YOOPITOOBO @ smazzneester > CREATE TABLE finish(
      2  actualfinish       DATE);

    TABLE created.

    YOOPITOOBO @ smazzneester >
    YOOPITOOBO @ smazzneester > INSERT INTO finish(actualfinish)
      2  VALUES(to_date('29/08/17 17:39','DD/MM/RR HH24:MI'));

    1 ROW created.

    YOOPITOOBO @ smazzneester >
    YOOPITOOBO @ smazzneester > commit;

    Commit complete.

    YOOPITOOBO @ smazzneester >
    YOOPITOOBO @ smazzneester > SELECT actualfinish FROM finish;

    ACTUALFIN
    ---------
    29-AUG-17

    YOOPITOOBO @ smazzneester >
    YOOPITOOBO @ smazzneester > ALTER SESSION SET nls_date_Format = 'DD/MM/RR HH24:MI';

    SESSION altered.

    YOOPITOOBO @ smazzneester >
    YOOPITOOBO @ smazzneester > SELECT actualfinish FROM finish;

    ACTUALFINISH
    --------------
    29/08/17 17:39

    YOOPITOOBO @ smazzneester >
    Notice that the NLS_DATE_FORMAT was changed to include most of the time portion of the date value (the seconds are missing).

    There is nothing wrong with the conversion.
     
    IlanM likes this.
  4. IlanM

    IlanM Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Mexico
    That was really helpful. Thanks for all.