December 8, 2003 at 12:20 pm
CONVERT(VARCHAR, DAY(GETDATE())) does return a varchar of todays date (eg 8)
Any way ican pad it to show me 08 instead (and not affecting when day > 9)
Like LPADs oracle funcion.
Thanks
December 8, 2003 at 12:42 pm
Try this:
RIGHT('0' + CONVERT(VARCHAR, DAY(GETDATE())),2)
Edited by - mdaniel on 12/08/2003 12:42:36 PM
December 8, 2003 at 1:08 pm
Thanks for the workaround mdaniel
December 9, 2003 at 7:52 am
There are several functions I have created to mimic behavior of other DBs' standard functions: LPAD, RPAD, DateTrunc, etc.
Here is the the basis for generic LPAD/RPAD functions. No error handling, as/is, no warranties, etc. I just happened to set the max string length to 50 - use whatever you want. There are separate articles available on how to put these in Master and make them available to all rather than having to invoke them as dbo.MyFunction.
Hope this helps.
Larry
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[LPad] (@InString varchar(50),@PadChar char(1), @PadLength smallint)
RETURNS varchar(50) AS
BEGIN
--"LeftPad" the user-supplied string to with the user-supplied character until the
--resulting string is the user-specifiec length. If the string starts longer
--than (or equal to) the intended pad length, just return the original string.
--Do not return an error or truncate the string.
WHILE (len(@InString) < @PadLength)
BEGIN
SET @InString = @PadChar + @InString
END
--to return the original string if too long, use the following
RETURN (@InString)
--to return a string of no longer than @PadLength, use the following
--RETURN (RIGHT(@InString,@PadLength))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[RPad] (@InString varchar(50),@PadChar char(1), @PadLength smallint)
RETURNS varchar(50) AS
BEGIN
--"RightPad" the user-supplied string to with the user-supplied character until the
--resulting string is the user-specifiec length. If the string starts longer
--than (or equal to) the intended pad length, just return the original string.
--Do not return an error or truncate the string.
WHILE (len(@InString) < @PadLength)
BEGIN
SET @InString = @InString + @PadChar
END
--to return the original string if too long, use the following
RETURN (@InString)
--to return a string of no longer than @PadLength, use the following
--RETURN (LEFT(@InString,@PadLength))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Test it
select dbo.LPad('foo','X',5)
select dbo.LPad('foobar','X',5)
select dbo.RPad('foo','X',5)
select dbo.RPad('foobar','X',5)
Larry
December 9, 2003 at 11:12 am
convert(char(2), getdate(), 3)
less typing
Chris Hofland
Chris Hofland
December 9, 2003 at 2:08 pm
An improvement to your LPAD function would be to replace the WHILE loop with
IF LEN(@InString) < @PadLength
SET @InString = REPLICATE(@PadChar, @PadLength - LEN(@InString))
(But I'd go with sql_servant's convert function for the original question)
December 10, 2003 at 9:30 am
hi!
for convenience, consider the following function (LPAD):
CREATE FUNCTION fn_lpad (
@p_value VARCHAR(50),
@p_char CHAR(1),
@p_digits INT) RETURNS VARCHAR(50) AS
BEGIN
RETURN REPLICATE(@p_char, @p_digits - LEN(@p_value)) + @p_value
END
GO
best regards,
chris.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply