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.