Calculate time differance after midnight

should be reasonably straight forward if they are datetime constructs... Could you please verify ?

In the meantime have a look at :

-- if datetime with legit dates and times
 
declare @start datetime
declare @end datetime
 
set @start = '21 May 2008 23:45:00'
set @end = '22 May 2008 03:15:00'
 
select convert(varchar(8),@end - @start,108), datediff(ss,@start,@end)
 
-- or if only time components, and stored as a string, then
 
declare @stime varchar(5)
declare @etime varchar(5)
 
set @stime = '23:45'
set @etime = '03:15'
 
select convert(varchar(8), case when @etime > @stime then (convert(varchar(20),getdate(),112) + ' '+@etime) else (convert(varchar(20),getdate() + 1,112) + ' '+@etime) end - (convert(datetime,convert(varchar(20),getdate(),112) + ' '+@stime)),108)

Select allOpen in new window

The "SELECT CASE" works to a degree, but an error apears "The Query Designer does not support the CASE SQL construct."

No, it doesn't. need to open up a query window, not the designer...

Hi tony, glad you found a solution - did you even look at my submission ? it will work for either datetime, or the formats you provided. further, it will put duration in the requested format of hh:mm:ss   and/or in seconds.

if they are date times, which is the only way your selected answer would work, then adding 1 to the date is an interesting concept, unless of course the date component is the system zero date of 01/01/1900 - which will also be part of the answer and not the format you had requested...

I used variables to demonstrate the syntax - is that the reason why you didn't look ? Or did it not achieve your results ? Would like to understand where I misunderstood your requirement.

Mark, thanks for your response, I am new to SQL and have only ever worked with access before using point and click queries.

I am using views within sql 2000 to create my query, i don't really know how or where to use your code

That would have been a good point to address...

A lot of people try to do all query work directly in Enterprise Manager. Really, a lot of it you should be  / could be running Query Analyser. It is purpose built for interactively running queries and Transact SQL.

You access it via the Tools drop down menu, or start,programs, microsoft sql server, query analyser. And is handy to right click from the pop-up programs menu and add to desktop.

You would then simply paste the code and run it - you can run pieces / groups at a time, by highlighting and using the F5 key to run.

The concept of using variables, at least in my posting, (they are the ones with declare and the @prefix) is so you can 'play' and look at the outcomes prior to using gobs of data...

to get it back into a 'useable' format, then simply replace the variable with the actual column names being used... Assuming they are actually datetime columns, then it would have been a matter of replacing the variables with your field names and include those new calculated columns in your view:

convert(varchar(8),item_timeb - item_timea,108) as diff_time, datediff(ss,item_timea,item_timeb) as diff_seconds,

But, the game is over and you have chosen a winner, so best to get back to work :)