Home » SQL & PL/SQL » SQL & PL/SQL » about sql query
about sql query [message #214002] Sat, 13 January 2007 08:32 Go to next message
testdba
Messages: 10
Registered: January 2007
Junior Member
Hi,
SQL> select sysdate ,'Time'||' '|| to_char(sysdate,'hh24') from dual
  2  /

SYSDATE              'TIME'|
-------------------- -------
13-jan-2007 18:29:19 Time 18

sir i want above query show result like 
13-jan-2007  time 01
             time 02
             time 03
             time 04
             ....
             ...
             time 23

with time show like 01 to 23 
all 24 hours.

how can write query for this.

regards
nomini.

[Updated on: Sat, 13 January 2007 08:33]

Report message to a moderator

Re: about sql query [message #214005 is a reply to message #214002] Sat, 13 January 2007 08:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check your NLS_DATE_FORMAT:

SQL> select value from nls_session_parameters
  2  where parameter = 'NLS_DATE_FORMAT';

VALUE
----------------------------------------
DD-MON-RR

SQL> select sysdate ,'Time'||' '|| to_char(sysdate,'hh24') from dual;

SYSDATE   'TIME'|
--------- -------
13-JAN-07 Time 09

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate ,'Time'||' '|| to_char(sysdate,'hh24') from dual;

SYSDATE              'TIME'|
-------------------- -------
13-jan-2007 09:44:38 Time 09

SQL> select to_char(sysdate, 'dd-mon-yyyy'), 'Time'||' '|| to_char(sysdate,'hh24
') from dual;

TO_CHAR(SYS 'TIME'|
----------- -------
13-jan-2007 Time 09
Re: about sql query [message #214006 is a reply to message #214005] Sat, 13 January 2007 08:52 Go to previous messageGo to next message
testdba
Messages: 10
Registered: January 2007
Junior Member
Hi,
thank for your reply sir.
but you are not understand my question in right way.
i want to query show every from 1 to 24.
SQL> select sysdate ,'Time'||' '|| to_char(sysdate,'hh24') from dual
  2  connect by rownum <= 23
  3  /

SYSDATE              'TIME'|
-------------------- -------
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18

SYSDATE              'TIME'|
-------------------- -------
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18

SYSDATE              'TIME'|
-------------------- -------
13-jan-2007 18:50:30 Time 18
13-jan-2007 18:50:30 Time 18

24 rows selected.

my query show only time 18 but i want start 01 and end with 23
hope this time i am clear

Re: about sql query [message #214009 is a reply to message #214006] Sat, 13 January 2007 09:05 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> break on sysdate
SQL> select * from
  2  (select sysdate ,'Time'||' '|| to_char(sysdate + (rownum)/24,'hh24')from dual
  3    connect by rownum <= 24)
  4  order by 2;

SYSDATE     'TIME'|
----------- -------
13-jan-2007 Time 00
            Time 01
            Time 02
            Time 03
            Time 04
            Time 05
            Time 06
            Time 07
            Time 08
            Time 09
            Time 10
            Time 11
            Time 12
            Time 13
            Time 14
            Time 15
            Time 16
            Time 17
            Time 18
            Time 19
            Time 20
            Time 21
            Time 22
            Time 23

24 rows selected.

[Updated on: Sat, 13 January 2007 09:06]

Report message to a moderator

Previous Topic: External tables and character set
Next Topic: pl/sql block
Goto Forum:
  


Current Time: Sun Dec 04 22:51:43 CST 2016

Total time taken to generate the page: 0.06983 seconds