Time calculation

Question

Post reply

Time calculation

pbaldy

SSC Eights!

Points: 956
More actions
March 1, 2004 at 4:29 pm

#107311

I'm relatively new to T-SQL, now converting Access queries to Stored Procedures for several Access front-end/SQL Server 2000 back-end applications. I've got the following in an Access query, which correctly gives me the elapsed time from a start and end field which could span midnight (both are date/time fields containing only time).
Format([StartTime]-1-[EndTime],"Short Time") AS TimeDiff
I can't seem to find the correct way to get this into T-SQL; tried different combinations of Convert(), but must have missed the right one. Any guidance would be appreciated.
Paul

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

declare @EndDate datetime , @StartDate datetime

Set @StartDate = '2005-08-01 11:30 AM'

Set @EndDate = '2005-08-02 1:30 AM'

select Convert(varchar(5), @startDate - @EndDate, 14) as TimeDiff

* Noel

pbaldy SSC Eights! Points: 956 More actions · Answer 2

Thanks noeld,

Unfortunately, as noted, my fields contain only times, so that doesn't return the correct result. I'll keep playing with it in the morning. My brain is fried at this point.

TriggerMe SSChampion Points: 11752 More actions · Answer 3

Depending on how you wish to calculate ie;Hours, Minutes or seconds. The following query should get you on the right track!

DECLARE @StartDate DATETIME, @EndDate DATETIME

SELECT DATEDIFF(mi, @StartDate, @EndDate) AS 'Time Diff'

Kindest Regards,

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 4

Pbaldy,

Another way of doing this is:

DATEDIFF(mi,

'01/01/1900 ' + CAST(@StartTime AS VARCHAR(5),

'01/01/1900 ' + CAST(@EndTime AS VARCHAR(5))

You will need to check BOL for the DateDiff variants I think that mi is good or it may be n. If you are ONLY storing the HH:MM in that format then the above will work if not you will need to manipulate the data to be

LEFT(@StartTime, 5) if you store HH:MM:SS.

Good Luck,

AJ

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

noeld had it right but swapped the @StartDate and @EndDate... his SELECT statment should have looked like this and THEN you would probably have the correct answer...

select Convert(varchar(5), @ENDDate - @STARTDate, 14) as TimeDiff

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 6

Jeff, thanks for the Correction I have to be a little more careful next time

About DateDiff --- That is not what the poster wanted. Because it will just return an integer Number

* Noel

pbaldy SSC Eights! Points: 956 More actions · Answer 7

Thanks to all who answered. I have it working now.

Paul

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 8

pbaldy,

Would you mind sharing what you did?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pbaldy SSC Eights! Points: 956 More actions · Answer 9

This is what I ended up with. It may not all be necessary, but I was trying different things and this finally worked. I had to move the formatting out to the Access report to convert the datediff integer to short time (13:45). I had separate date fields (not my design) which I ended up having to use. The Access formula was a great deal simpler, but of course it all runs faster as a stored procedure.

datediff(mi,convert(varchar(10),IsNull(date_start, '01/01/1900'),101) + ' ' + convert(varchar(5),IsNull(time_start, '00:01'),14),convert(varchar(10),date_end,101) + ' ' + convert(varchar(5),time_end,14)) as diff