Home » SQL & PL/SQL » SQL & PL/SQL » sql for date(formate)
sql for date(formate) [message #223362] Thu, 08 March 2007 12:10 Go to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
hello sir,


I have one question regarding sql date formate.

i have 2 columns start time and end time in my table

eg: my o/p start_t end_t
-------- --------
04:03:38 04:03:55
05:03:04 05:03:30
11:03:25 12:03:00

eg:04:03:55 - 04:03:38 = (00:00:17)i need this output

i want to subract this start time with end time plz help me to write sql query for this case.


Advanced thank u sir.
Re: sql for date(formate) [message #223373 is a reply to message #223362] Thu, 08 March 2007 13:11 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Have look at the sql reference at www.oracle.com

Search for sql reference and date and time functions

Re: sql for date(formate) [message #223374 is a reply to message #223362] Thu, 08 March 2007 13:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
here is a free clue
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref930
Re: sql for date(format) [message #223383 is a reply to message #223374] Thu, 08 March 2007 13:56 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
thank u for ur reply sir


but when i try with to_char,to_data conversion it shows error
SP2-0552: Bind variable "02" not declared

plz help me ..
Re: sql for date(format) [message #223386 is a reply to message #223383] Thu, 08 March 2007 14:07 Go to previous messageGo to next message
KrishnaBoppana
Messages: 12
Registered: March 2007
Location: Boston, MA
Junior Member
When you do date arithmetic, the result is in days. First you want to convert that into hours, secods, etc before you pass it to To_xxxx functions.

-Krishna
Re: sql for date(format) [message #223390 is a reply to message #223386] Thu, 08 March 2007 14:17 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
thank u sir

Actually i want to minus this format ,

>select 03:02-03:00 from dual;

o/p: 00:02

(this query shows error)


sir i am new to this field.plz help me..
Re: sql for date(format) [message #223392 is a reply to message #223390] Thu, 08 March 2007 14:23 Go to previous messageGo to next message
KrishnaBoppana
Messages: 12
Registered: March 2007
Location: Boston, MA
Junior Member
Try something like this -

select To_DATE('03:02','mi:ss') - To_DATE('03:00','mi:ss') from dual

SQL> select To_DATE('03:02','mi:ss') - To_DATE('03:00','mi:ss') from dual;

TO_DATE('03:02','MI:SS')-TO_DATE('03:00','MI:SS')
-------------------------------------------------
.000023148

SQL>

You have to figure out what the result is, as it is always in number of days with date arithmetic. So what you got is .000023148 days.


-Krishna

[Updated on: Sun, 27 January 2008 09:24] by Moderator

Report message to a moderator

Re: sql for date(formate) [message #223393 is a reply to message #223362] Thu, 08 March 2007 14:24 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member


Really thank u very much sir..thank u.

Re: sql for date(formate) [message #223413 is a reply to message #223362] Thu, 08 March 2007 16:11 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
just as an FYI, you should never store start and end times as a character string. What happens when your time starts on one day and ends on another. Always store it as a date or timestamp column. This will allow you to detemine the actual time span with simple mathematics.
Previous Topic: Drop Column
Next Topic: Print characters if no rows are selected
Goto Forum:
  


Current Time: Sun Dec 11 06:24:38 CST 2016

Total time taken to generate the page: 0.09118 seconds