Posts

Showing posts from June, 2009

Converting Int to Minutes,Hours andSeconds

DECLARE @SecondsToConvert int SET @SecondsToConvert = 3600 -- Declare variables DECLARE @Hours int DECLARE @Minutes int DECLARE @Seconds int DECLARE @Time datetime -- Set the calculations for hour, minute and second SET @Hours = @SecondsToConvert/3600 SET @Minutes = (@SecondsToConvert % 3600) / 60 SET @Seconds = @SecondsToConvert % 60 -- Store the datetime information retrieved in the @Time variable SET @Time = (SELECT RTRIM(CONVERT(char(8), @Hours) ) + ':' + CONVERT(char(2), @Minutes) + ':' + CONVERT(char(2), @Seconds)); -- Display the @Time variable in the format of HH:MMS SELECT CONVERT(varchar(8),CONVERT(datetime,@Time),108)

SPLIT Function

CREATE FUNCTION [DBO].[SPLITDELIMITED] ( @LIST NVARCHAR(2000), @SPLITON NVARCHAR(1) ) RETURNS @RTNVALUE TABLE ( ID INT IDENTITY(1,1), VALUE NVARCHAR(100) ) AS BEGIN WHILE (CHARINDEX(@SPLITON,@LIST)>0) BEGIN INSERT INTO @RTNVALUE (VALUE) SELECT VALUE = LTRIM(RTRIM(SUBSTRING(@LIST,1,CHARINDEX(@SPLITON,@LIST)-1))) SET @LIST = SUBSTRING(@LIST,CHARINDEX(@SPLITON,@LIST)+LEN(@SPLITON),LEN(@LIST)) END INSERT INTO @RTNVALUE (VALUE) SELECT VALUE = LTRIM(RTRIM(@LIST)) RETURN END