This discussion is archived
8 Replies Latest reply: Nov 14, 2012 6:28 AM by 968875 RSS

select time from date type without to_char

968875 Newbie
Currently Being Moderated
I have table T(myDate date, + about 15 columns). When I insert values into it, i also insert the time of a date.

If I wanna see all the data in the table, I need to call to_char on column myDate. So need to write all the 15 column names in the select.

I'd like to simply write a select * from T, but see the time for myDate as well. Is this possible?

Thanks
  • 1. Re: select time from date type without to_char
    971895 Journeyer
    Currently Being Moderated
    you mean say "sysdate as myDate ..can you explain in detail
  • 2. Re: select time from date type without to_char
    971895 Journeyer
    Currently Being Moderated
    Per my understaning try like

    select sysdate,t.* from emp t
  • 3. Re: select time from date type without to_char
    AlbertoFaenza Expert
    Currently Being Moderated
    You can use alter session in a SQLPlus session

    See this example:
    SQL> 
    SQL> select * from emp
      2  where rownum <=2;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
    
    SQL> 
    SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
    
    Session altered.
    
    SQL> 
    SQL> select * from emp
      2  where rownum <=2;
    
         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
    Regards.
    Al
  • 4. Re: select time from date type without to_char
    908002 Expert
    Currently Being Moderated
    Which editor you are using..


    If sql developer(seems for me), you can set tools -> preferences-> database-> and nls settings to dd-mon-yyyy hh:mi am to see the time when you use select * from my table;

    also, you can alter session to change nls format at sesion level.

    if you want to see that with query


    select t.*, to_char(mydate, 'dd-mon-yyyy hh:mi am') from mytable;

    Edited by: Kiran on Nov 14, 2012 2:52 AM
  • 5. Re: select time from date type without to_char
    968875 Newbie
    Currently Being Moderated
    Thanks, Alberto Faenza and Kiran, it's what I was looking for.
    Cheers

    Edited by: questioningq12 on Nov 14, 2012 3:04 AM
  • 6. Re: select time from date type without to_char
    BluShadow Guru Moderator
    Currently Being Moderated
    questioningq12 wrote:
    Thanks, Alberto Faenza and Kiran, it's what I was looking for.
    Cheers
    Though, for displaying dates, it is better to explicitly use TO_CHAR to specify the output date format, rather than rely on session specific parameters, which could change in a real life database.
  • 7. Re: select time from date type without to_char
    EdStevens Guru
    Currently Being Moderated
    questioningq12 wrote:
    I have table T(myDate date, + about 15 columns). When I insert values into it, i also insert the time of a date.

    If I wanna see all the data in the table, I need to call to_char on column myDate. So need to write all the 15 column names in the select.

    I'd like to simply write a select * from T, but see the time for myDate as well. Is this possible?

    Thanks
    Yes, but in less time than it has taken you to follow this thread, you could have coded up the column names -- which is better practice anyway. You should use "SELECT *" only in one-off, ad hoc queries.
  • 8. Re: select time from date type without to_char
    968875 Newbie
    Currently Being Moderated
    Agree, but I want to know all the alternatives. Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points