Time calculation
-
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
-
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 -
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.
-
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 -
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)
Intro to Tally Tables and Functions -
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 -
Thanks to all who answered. I have it working now.
Paul
-
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)
Intro to Tally Tables and Functions -
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply