| Sorting in Time Order [message #90206] |
Tue, 07 December 2004 05:50  |
Trina Cianfrocco
Messages: 1
Registered: December 2004
|
Junior Member |
|
|
We have an item within a table that is schedstart (which is the schedule start time). When I bring this item into the report is shows as a "Date" field (MMDDYYYY). I have converted this to a time (HH MM AM) field, however, the column does not sort in time order. It sorts in the Date order, which does not make sense. I am not sure how the date relates to the time?? Is there a calculation that will convert this field to time so that I can sort the report in time order?
Thanks.
|
|
|
|
| Re: Sorting in Time Order [message #90208 is a reply to message #90206] |
Tue, 07 December 2004 07:59  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
SQL> CREATE TABLE t (d DATE);
Table created.
SQL> INSERT INTO t
2 SELECT TO_DATE('200401','YYYYMM')
3 + DBMS_RANDOM.VALUE(0,366)
4 FROM sys.all_users
5 WHERE ROWNUM <= 20
6 /
20 rows created.
SQL> SELECT TO_CHAR(t.d
2 , 'MM/DD/YYYY HH:MI:SS AM') my_date
3 FROM t
4 ORDER BY t.d -- orders by the date, then time
5 /
MY_DATE
----------------------
01/13/2004 05:40:39 PM
02/14/2004 06:18:34 AM
02/19/2004 01:09:31 AM
02/27/2004 10:22:59 PM
03/03/2004 06:02:36 AM
03/28/2004 07:55:38 PM
04/28/2004 12:20:09 PM
05/09/2004 01:34:24 PM
05/23/2004 02:42:20 PM
07/02/2004 05:27:36 PM
07/05/2004 07:47:43 AM
07/12/2004 05:19:55 PM
07/17/2004 11:14:47 AM
07/17/2004 07:14:48 PM
08/03/2004 08:53:44 PM
08/17/2004 10:56:19 PM
09/20/2004 08:52:45 PM
09/27/2004 01:15:38 PM
10/05/2004 09:53:15 AM
11/17/2004 05:22:57 PM
20 rows selected.
SQL> SELECT TO_CHAR(t.d
2 , 'MM/DD/YYYY HH:MI:SS AM') my_date
3 FROM t
4 ORDER BY TO_CHAR(t.d,'SSSSS') -- orders by the time
5 -- without respect for date
6 /
MY_DATE
----------------------
02/19/2004 01:09:31 AM
03/03/2004 06:02:36 AM
02/14/2004 06:18:34 AM
07/05/2004 07:47:43 AM
10/05/2004 09:53:15 AM
07/17/2004 11:14:47 AM
04/28/2004 12:20:09 PM
09/27/2004 01:15:38 PM
05/09/2004 01:34:24 PM
05/23/2004 02:42:20 PM
07/12/2004 05:19:55 PM
11/17/2004 05:22:57 PM
07/02/2004 05:27:36 PM
01/13/2004 05:40:39 PM
07/17/2004 07:14:48 PM
03/28/2004 07:55:38 PM
09/20/2004 08:52:45 PM
08/03/2004 08:53:44 PM
02/27/2004 10:22:59 PM
08/17/2004 10:56:19 PM
20 rows selected.
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
