April 24, 2009 at 11:44 am
My developer is storing the time as a 6 char string, e.g. 133737 for 1:37:37 PM. I'm trying to subtract fields to determine number of hours minutes seconds between events for elapsed time, but I can't seem to get CAST or CONVERT to give me the required format.
Row 1 154040
Row 2 133737
So, (Row 1 - Row 2) = 2h3m3s or 2.05h
The sun is bright today, but I'm not.
April 24, 2009 at 12:30 pm
First, fire your developer and store the time in a datetime field.
In the meantime, use this code:
declare @Start char(6), @End char(6)
set @Start = '133737'
set @End = '154040'
declare @StartTime datetime, @EndTime datetime
set @StartTime = stuff(stuff(@Start,3,0,':'),6,0,':')
set @EndTime = stuff(stuff(@End,3,0,':'),6,0,':')
select @StartTime, @EndTime, convert(char(8), @EndTime - @StartTime, 108)
After you've changed the fields to datetime, all you would have to do is:
select convert(char(8), EndDate - StartDate, 108)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 24, 2009 at 12:40 pm
Before you start stuffing the string, make sure that times before 10 AM are stored with leading zeroes. If not, you might have a problem when 9:00 AM tries to turn into the hour 90.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2009 at 12:43 pm
By converting the time strings to datetime values, you can do the following.
/* I'm assuming here that @TimeString1 always represents the earlier time and @TimeString2 the later time */
DECLARE @TimeString1 char(6)
DECLARE @TimeString2 char(6)
SELECT @TimeString1 = '133737', @TimeString2 = '154040'
/* Insert ':' characters to format as 'HH:MM:SS' and implicitly convert strings to datetime values */
DECLARE @Time1 datetime
DECLARE @Time2 datetime
SELECT @Time1 = STUFF(STUFF(@TimeString1, 5, 0, ':'), 3, 0, ':'),
@Time2 = STUFF(STUFF(@TimeString2, 5, 0, ':'), 3, 0, ':')
/* Determine time difference as a datetime value relative to value 0 (1900-01-01 00:00:00.000). Note that if @Time1 > @Time2 then the result of the DATEDIFF function call is negative. In this case, it is assumed that the two times span a day boundary so 24 * 60 * 60 = 86400 seconds need to be added to get the true elapsed time. */
DECLARE @ElapsedTime datetime
SELECT @ElapsedTime = DATEADD(second, DATEDIFF(second, @Time1, @Time2) + CASE WHEN (@Time1 > @Time2) THEN 86400 ELSE 0 END, 0)
/* Finally display elapsed time using CONVERT format 108 */
SELECT CONVERT(char(8), @ElapsedTime, 108)
If elapsed times equal or exceed 24 hours then the above method won't work and you will need to consider dates as well.
It would have been simpler and more efficient to store the times as datetime values in the table, and if you are storing the date in a separate column, then it would probably be better to store the date and time combined in a single datetime column as well.
April 24, 2009 at 12:51 pm
Here's a one-command approach for the first one I did. It's uglier, but you don't have to call any procedures / functions.
select convert(char(8),
convert(datetime,
convert(datetime, stuff(stuff('154040',3,0,':'),6,0,':')) -
convert(datetime, stuff(stuff('133737',3,0,':'),6,0,':'))),
108)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 24, 2009 at 1:29 pm
Thanks guys. I've been away from SQL for a while. This really helped.
April 24, 2009 at 3:51 pm
WayneS (4/24/2009)
First, fire your developer and store the time in a datetime field.
BWAA-HAA-HAA!!!! Dammit! Ya beat me to it! 😛 Wonder if the developer likes point blank pork chops? :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2009 at 7:55 pm
GSquared (4/24/2009)
Before you start stuffing the string, make sure that times before 10 AM are stored with leading zeroes. If not, you might have a problem when 9:00 AM tries to turn into the hour 90.
Hmm, good point.
How about this?
select convert(char(8),
convert(datetime,
convert(datetime, stuff(stuff(right('0' + '154040',6),3,0,':'),6,0,':')) -
convert(datetime, stuff(stuff(right('0' + '133737',6),3,0,':'),6,0,':'))),
108)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 25, 2009 at 9:08 am
WayneS (4/24/2009)
GSquared (4/24/2009)
Before you start stuffing the string, make sure that times before 10 AM are stored with leading zeroes. If not, you might have a problem when 9:00 AM tries to turn into the hour 90.Hmm, good point.
How about this?
select convert(char(8),
convert(datetime,
convert(datetime, stuff(stuff(right('0' + '154040',6),3,0,':'),6,0,':')) -
convert(datetime, stuff(stuff(right('0' + '133737',6),3,0,':'),6,0,':'))),
108)
Good idea... but what about times before 1 AM? For example, 000001 should be interpreted as 00:00:01 and it probably won't because it seems that all the leading zeros are dropped... probably because the original time was stored as an INT or something odd. I say "odd", but even Microsoft made the same terrible mistake in the MSDB.dbo.SysJobsHistory.
With that in mind, you might want to change the code to look like this...
select convert(char(8),
convert(datetime,
convert(datetime, stuff(stuff(right('000000' + '154040',6),3,0,':'),6,0,':')) -
convert(datetime, stuff(stuff(right('000000' + '133737',6),3,0,':'),6,0,':'))),
108)
Still, that doesn't solve the real problem... the real problem is that the times aren't stored as a DATETIME datatype which would greatly simplify such mathematics.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2009 at 7:28 am
To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 27, 2009 at 7:28 pm
GSquared (4/27/2009)
To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.
As is usually the case, absolutely correct. But, reversing it is relatively expensive.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2009 at 11:18 pm
Jeff Moden (4/27/2009)
GSquared (4/27/2009)
To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.As is usually the case, absolutely correct. But, reversing it is relatively expensive.
Both of the methods suck.
🙂
_____________
Code for TallyGenerator
April 27, 2009 at 11:59 pm
Sergiy (4/27/2009)
Jeff Moden (4/27/2009)
GSquared (4/27/2009)
To deal with times that don't have enough leading zeroes, you can either padd the string, or you can reverse it. Either one works.As is usually the case, absolutely correct. But, reversing it is relatively expensive.
Both of the methods suck.
🙂
Heh... Long time no see. Speaking of "see"... got a method that doesn't suck so much? I've forgotten the pure math solution because I don't allow such garbage into my db's to begin with. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2009 at 6:57 am
Off the bat- First post 😀
Also- This might be a little bit sloppy (but everything else in this thread is a bit sloppy thus far :-))
declare @Inttime int
set @Inttime = 133737
declare @hour int
declare @minute int
declare @second int
declare @time datetime
set @time = '00:00:00'
SET @hour = @Inttime / 10000
SET @minute = ((@Inttime - @hour*10000) / 100)
SET @second = ((@Inttime - @hour*10000) - @minute * 100)
SET @time = DATEADD(hh, @hour, @time)
SET @time = DATEADD(mi, @minute, @time)
SET @time = DATEADD(s, @second, @time)
select @hour, @minute, @second
SELECT @time
*EDIT - I accidentally posted the "test" of trying just 1, I also apparently misread the entire thread and was trying to do this on an int 😛
April 28, 2009 at 7:53 am
Here's an alternative query that uses a method similar to Mike McQueen's to parse the times from the time strings when converted to integers. This method does have the advantage that you don't need to worry about leading zeros. The query assumes that @time1 is earlier than @time2 so that if @time2 < @time1 then the times must span a (single) day boundary and amends the result accordingly.
DECLARE @time1 int
DECLARE @time2 int
SELECT @time1 = CAST('235030' AS int), @time2 = CAST('21029' AS int)
SELECT CONVERT(varchar(8), DATEADD(second,
DATEDIFF(second,
DATEADD(second, @time1 % 100, DATEADD(minute, (@time1 / 100) % 100, DATEADD(hour, (@time1 / 10000), 0))),
DATEADD(second, @time2 % 100, DATEADD(minute, (@time2 / 100) % 100, DATEADD(hour, (@time2 / 10000), 0)))),
CASE WHEN (@time2 < @time1) THEN 1 ELSE 0 END), 108)
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply