how to extract hh:mm from timestamp ? [message #618335] |
Thu, 10 July 2014 23:45 |
tondapi
Messages: 99 Registered: August 2007 Location: usa
|
Member |
|
|
Hi DBA's,
I have a questions, how to get hh:mm from timestamp(7/9/2014 00:02:09.710000). When I use below condition I can extrct but I am not able to apply any condition
CAST(EXTRACT(HOUR FROM TIME) AS CHAR(2)) ||' '||':'||' '|| CAST(EXTRACT(MINUTE FROM TIME) AS CHAR(2))
Condition I am applying like time_as between '02:00' and '03:00' is not working, because extract is not happen properly.
Result
TIME TIME_AS
7/10/2014 00:02:18.760000 0 : 2
7/10/2014 10:02:06.130000 10 : 2
7/10/2014 02:05:42.910000 2 : 5
7/10/2014 02:05:43.080000 2 : 5
7/10/2014 03:00:13.950000 3 : 0
7/10/2014 03:00:09.140000 3 : 0
7/10/2014 03:00:15.220000 3 : 0
7/10/2014 03:08:45.630000 3 : 8
7/10/2014 03:08:58.510000 3 : 8
7/10/2014 03:09:36.770000 3 : 9
7/10/2014 03:09:06.640000 3 : 9
7/10/2014 03:09:48.660000 3 : 9
7/10/2014 03:09:56.340000 3 : 9
7/10/2014 03:09:36.750000 3 : 9
7/10/2014 03:09:14.230000 3 : 9
7/10/2014 03:09:48.640000 3 : 9
7/10/2014 03:09:04.170000 3 : 9
7/10/2014 04:05:08.360000 4 : 5
7/10/2014 05:01:48.350000 5 : 1
7/10/2014 05:02:30.790000 5 : 2
7/10/2014 05:02:30.800000 5 : 2
|
|
|
|
|
Re: how to extract hh:mm from timestamp ? [message #618343 is a reply to message #618335] |
Fri, 11 July 2014 02:28 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
tondapi wrote on Fri, 11 July 2014 10:15how to get hh:mm from timestamp
It should be "mi" for MINUTES. "mm" is for MONTH.
As Michel suggested to use TO_CHAR, you could use the following format mask to achieve the desired output :
SQL> column tm_stamp format a33;
SQL>
SQL> WITH tm
2 AS (SELECT To_timestamp('7/9/2014 00:02:09.710000',
3 'mm/dd/yyyy hh24:mi:ss.ff')
4 tm_stamp
5 FROM dual)
6 SELECT tm_stamp,
7 To_char(tm_stamp, 'hh24:mi') time_as
8 FROM tm;
TM_STAMP TIME_AS
--------------------------------- -------
09-JUL-14 12.02.09.710000000 AM 00:02
Have a look at it in documentation, TO_TIMESTAMP
|
|
|