Home » SQL & PL/SQL » SQL & PL/SQL » how to extract hh:mm from timestamp ?
how to extract hh:mm from timestamp ? [message #618335] Thu, 10 July 2014 23:45 Go to next message
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 #618336 is a reply to message #618335] Thu, 10 July 2014 23:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Condition I am applying like time_as between '02:00' and '03:00'

characters between single quote marks are strings
never rely on implicit datatype conversion

it is not clear what datatype is input source.
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/


Re: how to extract hh:mm from timestamp ? [message #618338 is a reply to message #618335] Fri, 11 July 2014 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how to get hh:mm from timestamp(7/9/2014 00:02:09.710000).


TO_CHAR (Of course if timestamp is really of one of TIMESTAMP datatypes).

Re: how to extract hh:mm from timestamp ? [message #618343 is a reply to message #618335] Fri, 11 July 2014 02:28 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
tondapi wrote on Fri, 11 July 2014 10:15
how 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
Previous Topic: SQL
Next Topic: to find mid value
Goto Forum:
  


Current Time: Fri Mar 29 06:26:21 CDT 2024