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!

Date functions.,.

Discussion in 'SQL PL/SQL' started by Vicky, Jun 20, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Could you tell me why the query below doesn't return time?!


    select to_date('05-06-2012 03:56', 'DD-MM-YYYY HH24:MI') from dual;
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    TO_DATE converts the string to a date using the format you've provided and why you're getting only the date portion is because your default date display format doesn't include the time portion. This is governed by the NLS_DATE_FORMAT parameter.

    Try the following:
    Code (SQL):

    SQL> SELECT to_date('05-06-2012 03:56', 'DD-MM-YYYY HH24:MI') dateonly  FROM dual;

    DATEONLY
    ------------------
    05-JUN-12

    SQL> SELECT name, VALUE FROM v$parameter WHERE name = 'nls_date_format';

    NAME             VALUE
    -------------------- --------------------
    nls_date_format      DD-MON-RR

    -- You see VALUE matches with date display format


    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI';

    SESSION altered.

    SQL>  SELECT to_date('05-06-2012 03:56', 'DD-MM-YYYY HH24:MI') datentime FROM dual;

    DATENTIME
    --------------------------
    05-JUN-2012 03:56
     
    Vicky likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Vicky likes this.