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, Oct 15 '15 at 19:48
try this link if it helps it has many options for same http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008 — narendra, Oct 15 '15 at 19:52
Tons of options shown here http://stackoverflow.com/questions/1914682/t-sql-format-integer-to-2-digit-string — Aaron Dietz, Oct 15 '15 at 19:53
What you have with the `right` function is a good solution and imo cleaner than using IF/CASE statements. — John Odom, Oct 15 '15 at 19:55
@lad2025 the `TSQL` tag itself mentions that `TSQL` also works in `Sybase`, so it's not true that `TSQL`=>`SQL Server`. However, TSQL's syntax should remain the same across both platforms. — Julien Blanchard, Oct 15 '15 at 20:09

Ronny · Accepted Answer · 2015-10-15T19:56:29.317

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 :) — Ronny, Oct 15 '15 at 19:58

score 1 · Answer 2 · answered Oct 15 '15 at 19:54

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.

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

2 Answers2