TAman (7/29/2009)
I'm not sure what you are representing when you say "stuff"("stuff"And what does the '00000' represent?
Select *
,stuff(stuff(right('00000' + cast(TimeValue As varchar(6)), 6), 5, 0, ':'), 3, 0, ':') As NewTimeValue
From #test;
What the "00000" is doing is to make your string longer than necessary. You then take the right 6 values, making a "short" time (i.e. 80019) be the proper length (i.e. 080019).
The stuff function is fully documented in BOL at http://msdn.microsoft.com/en-us/library/aa259350(SQL.80).aspx. Basically, it is "stuffing" the colons into the time string. Each "stuff" call is putting one colon in; thus it takes two calls to get the time correct.
Okay, I have created this code to convert the bigint into time.
SELECT *, CONVERT(BIGINT(6), ACT_TIME, 114) AS NEW_ACT_TIME
FROM dbo.tbl_table
I receive this error:
Msg 291, Level 16, State 1, Line 1
CAST or CONVERT: invalid attributes specified for type 'bigint'
The bigint datatype does not support a size declaration; thus the "(6)" is invalid. This is what the error message is saying.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes