Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
nandhini
Starting Member
3 Posts |
Posted - 2013-03-14 : 07:35:07
|
Hi All,I have a sql query which returns me the start and end time of certain process, i just need another output being displayed beside, which gives me the difference of end time and start time, im able to do that by just subtracting end time minus start time, but the difference returns me null.i have pasted the query below,select PROCESS.START_TIME, PROCESS.END_TIME, (PROCESS.END_TIME-PROCESS.START_TIME)As Difference from DOCKETS_PUB.PROCESSthis difference column returns me null, im doing this in a dbvisualizer.Any help on this would be appreciated.Thanks,Nandhininandhini |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-14 : 07:39:08
|
In sql server DATEDIFF function is available to get the difference in the form of days, months, years and so on.........DATEDIFF( DD, PROCESS.START_TIME, PROCESS.END_TIME)--Chandu |
|
|
nandhini
Starting Member
3 Posts |
Posted - 2013-03-15 : 01:50:51
|
Thanks for the reply bandi,Below is my actual query,select EVENT.event_name, EVENT.processed_flag, PROCESS.START_TIME, PROCESS.END_TIME from D_PUB.EVENT, D_PUB.PROCESS where EVENT.processed_flag ='Y'and PROCESS.batch_id = EVENT.batch_id and PROCESS.sub_batch_id = EVENT.sub_batch_id.This query output gives me the process, flag, start time and end time,But i also need the difference of time stamp of end time-start time,As u told the datediff gives only difference of date, but i need the difference of whole timestamp,the start or end time looks something in this format2013-01-17 14:28:13,Any help on this would be appreciated.Thanks.nandhini |
|
|
Paramasivan B
Starting Member
5 Posts |
Posted - 2013-03-15 : 02:21:17
|
quote: Originally posted by nandhini Thanks for the reply bandi,Below is my actual query,select EVENT.event_name, EVENT.processed_flag, PROCESS.START_TIME, PROCESS.END_TIME from D_PUB.EVENT, D_PUB.PROCESS where EVENT.processed_flag ='Y'and PROCESS.batch_id = EVENT.batch_id and PROCESS.sub_batch_id = EVENT.sub_batch_id.This query output gives me the process, flag, start time and end time,But i also need the difference of time stamp of end time-start time,As u told the datediff gives only difference of date, but i need the difference of whole timestamp,the start or end time looks something in this format2013-01-17 14:28:13,Any help on this would be appreciated.Thanks.nandhini
Just try this...DATEDIFF(HH, PROCESS.START_TIME, PROCESS.END_TIME)DATEDIFF(MI, PROCESS.START_TIME, PROCESS.END_TIME)Then concadenate both of thisJust Check and confirmParamasivan B |
|
|
nandhini
Starting Member
3 Posts |
Posted - 2013-03-18 : 01:21:55
|
Hi...Thanks for the reply,But when i edit using the DATEDIFF as you told, it throws the following error,10:48:13 [SELECT - 0 row(s), 0.000 secs] [Error Code: 904, SQL State: 42000] ORA-00904: "DATEDIFF": invalid identifier... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]i have experienced this before also when i tried with DATEDIFF format.Thanksnandhini |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
|
|
|
|
|