Trouble putting a zero at the front of a single digit variable?

• david.dartnell

  Ten Centuries

  Points: 1276

  More actions

  September 28, 2014 at 7:42 pm

  #311894

  Hi Everyone,

  I am retrieving the current month and setting a variable accordingly.

  DECLARE @currMonthNum INT

  SET @currMonthNum = DATEPART(MM, GETDATE())

  My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).

  Here is my attempt to do this...!

  IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9)

  SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)

  Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.

  I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).

  If anybody can share some wisdom on how to display a zero in front of the months that require it I will greatly appreciate it.

  Kind Regards,

  David

• Koen Verbeeck

  SSC Guru

  Points: 259015

  More actions

  September 29, 2014 at 1:07 am

  #1748714

  If you want a leading zero, you'll need to store it as a string, not as an int.

  By the way, a much easier expression is this:

  SET @currMonthNum = RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(MM, GETDATE())),2)

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP