SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Query Formatting Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nandhini
Starting Member

India
3 Posts

Posted - 03/14/2013 :  07:35:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/14/2013 :  07:39:08  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 03/15/2013 :  01:50:51  Show Profile  Reply with Quote
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

India
4 Posts

Posted - 03/15/2013 :  02:21:17  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 03/18/2013 :  01:21:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/18/2013 :  01:34:19  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000