sql date formatter

[nandhini]

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.

 

[KB]

Whats your DBMS? Many do it differently, but in SQL Server you would use the DATEDIFF function to get the difference between dates. If one of the dates is null you get null as the output.

http://msdn.microsoft.com/en-us/library/ms189794.aspx

 

[nandhini]

Hi,
Thanks for the reply KB,

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.

 

[KLin]

Please answer KB's other question. What database engine is this? And what unit of time do you need to see the the difference? Seconds? Minutes?

 

[KB]

Subtracting timestamp fields in SQL Server doesn't make sense. "The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type."

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

For this reason I think you must be using Oracle then?
You can get the difference using subtraction in an oracle timestamp field. Make sure the END_TIME and START_TIME columns are both filled in and not NULL.

 

[ForumMaster]

Subtracting timestamp fields in SQL Server doesn't make sense. "The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type."

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

For this reason I think you must be using Oracle then?
You can get the difference using subtraction in an oracle timestamp field. Make sure the END_TIME and START_TIME columns are both filled in and not NULL.

you're correct that oracle disallows subtracting timestamp datatype columns,
however it allows subtraction/addition of data columns just fine.

the result will be of data type "interval day second".

for example, (In oracle!), select (sysdate) - (sysdate - 1.5) from dual
will obviously return 1.5. however this 1.5 is actually in days.
applying the numtodsinterval(column_name,'DAYS') function on it will convert
it from a number to a proper interval data type.

an again though, which database are you using? the syntax is different between the different rdbms's.