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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query Formatting Date

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.PROCESS

this difference column returns me null, im doing this in a dbvisualizer.


Any help on this would be appreciated.


Thanks,
Nandhini

nandhini

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
Go to Top of Page

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 format
2013-01-17 14:28:13,
Any help on this would be appreciated.

Thanks.


nandhini
Go to Top of Page

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 format
2013-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 this
Just Check and confirm


Paramasivan B
Go to Top of Page

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.



Thanks

nandhini
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 01:34:19
ORA-00904 means you are using Oracle...right?
Check this link once...
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129

Better to post Oracle posts in dbforums.com to get quick response... Any way SQLTEAM.com is SQL Server forum

--
Chandu
Go to Top of Page
   

- Advertisement -