8 Replies Latest reply: Nov 14, 2012 8:28 AM by 968875 RSS

    select time from date type without to_char

    968875
      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
          you mean say "sysdate as myDate ..can you explain in detail
          • 2. Re: select time from date type without to_char
            971895
            Per my understaning try like

            select sysdate,t.* from emp t
            • 3. Re: select time from date type without to_char
              AlbertoFaenza
              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
                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
                  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
                    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
                      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
                        Agree, but I want to know all the alternatives. Thanks.