Home » SQL & PL/SQL » SQL & PL/SQL » instr function
instr function [message #218932] Mon, 12 February 2007 02:54 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
hello

i am having a table, name tim

SQL> select * from tim;

NAME         TIME
------------ --------------------
ashish       01-feb-2007 01:23:00
ashish       01-feb-2007 01:45:00
raju         01-feb-2007 01:20:00
ashish       01-feb-2007 01:28:00
raju         01-feb-2007 04:23:00

5 rows selected.


here i want to retrieve value of time
like as

01
01
01

i had done a query but failed to get the result


SQL> ed
Wrote file afiedt.buf

  1  select substr(time, instr(time, ' ')+1,
  2*  instr(time, ':', 1 , 1))as time from tim
SQL> /

TIME
--------------------
01:23:00
01:45:00
01:20:00
01:28:00
04:23:00

5 rows selected.


please help me to solve the problem.


regards
Re: instr function [message #218933 is a reply to message #218932] Mon, 12 February 2007 03:00 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


why use substr???

select to_char(time,'hh')time_part from tim



regards,
Re: instr function [message #218965 is a reply to message #218932] Mon, 12 February 2007 05:00 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
thanx dhananjay

u solved my a bit of my problem.
please solve this also.
as per your suggestion i did the query on 'mm' but failed to get the result.
SQL> select to_char(time, 'mm')"minutes" from tim;

mi
--
02
02
02
02
02

5 rows selected.



please help once more.

thanx

with regards
Re: instr function [message #218971 is a reply to message #218932] Mon, 12 February 2007 05:08 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

you need to use 'mi' for minutes. 'mm' is for the month.

Re: instr function [message #218974 is a reply to message #218932] Mon, 12 February 2007 05:43 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
thankyou scorpio_biker

i mis-understand dhananjay's tips.
again thankyou mr.dhananjay & scorpio_biker.

regards
Re: instr function [message #219020 is a reply to message #218932] Mon, 12 February 2007 10:39 Go to previous message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi,

Try doing this

Select to_char(time,'hh') from tim;

This will give you the time part alone. If you want this as a number field then do this

Select to_number(to_char(time,'hh')) as hours from tim;

Thanks
Panneer Selvam
Previous Topic: How to use a sequence to populate a composite PK key
Next Topic: Methods
Goto Forum:
  


Current Time: Wed Dec 07 04:35:50 CST 2016

Total time taken to generate the page: 0.12280 seconds