Tuesday, December 1, 2009

UD function to add leading zeros

The other day I needed to add leading zero to hour and minute parts of the time (listed as integer types) if there was only one digit, and do nothing if there were two digits.

For example I had 4:55 and I needed it to show up as 04:55, or 11:7 to show up as 11:07.

So I used

SELECT dbo.AddLeadingZero(@hour) + ':' + dbo.AddLeadingZero(@minute)



And here is the function I utilized



CREATE FUNCTION dbo.AddLeadingZero
(@val INT)
RETURNS VARCHAR(2)
AS

BEGIN

DECLARE @length INT
DECLARE @output VARCHAR(2)

SET @length=LEN(CAST(@val as varchar))

IF @length > 1
BEGIN
SET @output=CAST(@val as varchar)
END
ELSE
BEGIN
SET @output= '0' + CAST(@val as varchar)
END
RETURN @output
END

No comments:

Post a Comment