Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Date datatype does not display time when selected

    I am very new to Oracle, and I am using Oracle Application Express 3.0.1.00.08.

    I have created a table using the date datatype, and stored values like this:

    insert into bokningar(StartTid, BokTid)
    values(to_date('2009/06/22 10:00:00am', 'yyyy/mm/dd hh:mi:ssam'), to_date('2009/06/22 09:05:00am', 'yyyy/mm/dd hh:mi:ssam'));

    But when I choose SELECT *from bokningar what I get is this:

    STARTTID BOKTID
    22-Jun-09 22-Jun-09

    I cannot figure out why the time is not displayed?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Because default date format is set to DD-MON-YY. If you want to change it, do that by altering the session as
    Code:
    SQL> alter session set nls_date_format = 'dd-mon-yy';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    16-lip-09
    
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select sysdate from dual;
    
    SYSDATE
    -------------------
    16.06.2009 21:28:14
    
    SQL>

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Thanks for the reply!

    That code doesn't change anything, neither does it show the time. I tried it:


    SQL> alter session set nls_date_format = 'dd-mon-yy';

    Statement processed.

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    16-JUN-09

    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

    Statement processed.

    SQL> select sysdate from dual;

    SYSDATE
    ---------
    16-JUN-09


    I don't care in what format the date is shown, I just want it to show the time which at the moment it doesn't! Sorry if I'm being daft, I have just started to learn about databases.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by ozymandias2
    SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

    Statement processed.
    This doesn't look like the feedback of SQL*Plus.
    In which client are you running this?

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    It's Oracle Application Express 3.0.1.00.08.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Formatting of values (not only dates, but also numbers) is completely up to the client.

    You will need to check the manual for Application Express on how to define the date format.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> alter system set nls_date_format = 'YYYY-MM-DD hh24:mi:ss' scope=spfile;

    System altered.

    & then restart the DB
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by anacedent
    SQL> alter system set nls_date_format = 'YYYY-MM-DD hh24:mi:ss' scope=spfile;

    System altered.

    & then restart the DB
    That is a pretty risky workaround because it could break applications that expect a certain (the current) NLS_DATE_FORMAT

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Excuse me, write your query as

    select to_char(sysdate,'MM/DD/YYYY HH24:MI.SS') from dual;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •