Avatar of tonyfuzzy
tonyfuzzy asked on

Calculate time differance after midnight

Hello,

I am trying to write a SQL query for determining the amount of time between times in a table, that overlap midnight. I would like to have the time in seconds between the two times or idealy (HH:MM:SS), have tried datediff but comes up with a minus figure.

My table consists of an ID, start and finish time.

EG:
------------------------------------------------------
|   ID    |  Item_TimeA    |  Item_TimeB  |
------------------------------------------------------
00001 |    14:32         |       00:53
00002 |    23:54         |       00:34
00003 |    23:55         |       01:14
------------------------------------------------------



Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Wills

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)

Open in new window

ASKER
tonyfuzzy

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

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Wills

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.
ASKER
tonyfuzzy

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
Mark Wills

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 :)


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.