How do I get the month and day with leading 0's in SQL? (e.g. 9 => 09)

Question

DECLARE @day CHAR(2)

SET @day = DATEPART(DAY, GETDATE())

PRINT @day

If today was the 9th of December, the above would print "9".

I want to print "09". How do I go about doing this?

Solution 1

For SQL Server 2012 and up , with leading zeroes:

 SELECT FORMAT(GETDATE(),'MM') 

without:

SELECT    MONTH(GETDATE())

Solution 2

Pad it with 00 and take the right 2:

DECLARE @day CHAR(2)

SET @day = RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2)

print @day

Solution 3

Use SQL Server's date styles to pre-format your date values.

SELECT
    CONVERT(varchar(2), GETDATE(), 101) AS monthLeadingZero  -- Date Style 101 = mm/dd/yyyy
    ,CONVERT(varchar(2), GETDATE(), 103) AS dayLeadingZero   -- Date Style 103 = dd/mm/yyyy

Solution 4

Try this :

SELECT CONVERT(varchar(2), GETDATE(), 101)

Solution 5

Leading 0 day

SELECT FORMAT(GetDate(), 'dd')

View more solutions

Comments

[JJ.]

geeze, that code looks horrific! haha. but thanks. will give it a try.

[twoleggedhorse]

I have updated it because I did a -1 inside the GETDATE() - Today is the 10th and I wanted the 9th so that I could test it...Use the updated code.

[rrrr-o]

Out of curiosity, any particular reason for an NVARCHAR and the double zero?

[twoleggedhorse]

Using CHAR will not work, you can use VARCHAR or NVARCHAR. I usually use nvarchar because I use unicode and it's a force of habit. Whenever I'm padding, I always use the same number of characters as the answer - just a personal preference, you can just use a single zero if you wish.

[twoleggedhorse]

Replicate!! That's the word I was looking for...I was thinking lpad, pad, couldn't remember for the life of me :)

[Tony Hopkinson]

I like your version better as the convert is only called once.

[Sajjan Sarkar]

@twoleggedhorse yep, it's 2016 now :)

[twoleggedhorse]

Most people do not have the latest version of SQL, a lot will still be on 2008 or less, so it is important to let them know when answers only work with specific versions.

[Craig Silver]

Okay, I think that's pretty smart!

[Soulfire]

As someone who is stuck on 2008 R2 at work, I always appreciate when people let me know which version of SQL server their code is for.

[Dave Lucre]

I suspect you're being downvoted because this is a fairly low quality answer, you didn't even put it in a code block. Also, I am not entirely sure why you would pad the year with Zeros, but maybe I am missing something.

[Steve Scott]

what is 101 and 103?

[Daniel L. VanDenBosch]

Take note I did test this functions on days and it does lead the zeros