How to append 0 to a value in SQL Server if value is 1 character using Padding function

Question

I have a stored procedure that returns YYYYMMDD formatted string.

I'm looking for the right way to create a string of MM and DD if the string supplied has only 1 character.

For example:

If day provided is 1, it needs to be changed to 01 and the same for the month.

declare @day as int declare @month as int select @day = 1 select @month = 9 Formatting code select @day // gives 01 select @month // gives 09

I do not want to use if logic here to check the length. Is that possible to use some kind of Formatting functionality to achieve the same result?

I have found something like:

select right('0' + cast(@day as varchar(2)),2)

Would that be the correct solution?

usually text function are specify to rdbms, what is yours? sqlserver, oracle, mysql? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 15, 2015 at 19:48
try this link if it helps it has many options for same stackoverflow.com/questions/16760900/… — narendra
– narendra, Commented Oct 15, 2015 at 19:52
Tons of options shown here stackoverflow.com/questions/1914682/… — Aaron Dietz
– Aaron Dietz, Commented Oct 15, 2015 at 19:53
What you have with the right function is a good solution and imo cleaner than using IF/CASE statements. — John Odom
– John Odom, Commented Oct 15, 2015 at 19:55

Ronny · Accepted Answer · 2015-10-15 19:56:29Z

You could try the following. I know that you said you don't want to use an IF statement, and I'm probably splitting hairs by supplying a CASE statement, but it is an alternative to IF:

declare @day as int declare @month as int select @day = 1 select @month = 9 Formatting code select CASE WHEN LEN(@day) = 1 THEN '0' + @day ELSE @day END // gives 01 select CASE WHEN LEN(@month) = 1 THEN '0' + @month ELSE @month END // gives 09

You could also try:

declare @day as int declare @month as int select @day = 1 select @month = 9 Formatting code select RIGHT('0'+cast(@day AS varchar(2)), 2) // gives 01 select RIGHT('0'+cast(@day AS varchar(2)), 2) // gives 09

Looks like you've already found the RIGHT() solution. I guess I should have read a bit further past your posted code before providing the alternative :)

D Stanley · Accepted Answer · 2015-10-15 19:54:10Z

Would that be the correct solution?

Does it give you the results you want? Then yes it is one solution. There's nothing wrong with it.

I will say a stored procedure seems like an odd choice for this function. Stored procedures are usually used to return sets. A scalar function may be a better choice.

Collectives™ on Stack Overflow

How to append 0 to a value in SQL Server if value is 1 character using Padding function

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related