How do I get the month and day with leading 0'

问题:

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?

回答1:

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

回答2:

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

回答3:

For SQL Server 2012 and up , with leading zeroes:

 SELECT FORMAT(GETDATE(),'MM') 

without:

SELECT    MONTH(GETDATE())

回答4:

Try this :

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

回答5:

Leading 0 day

SELECT FORMAT(GetDate(), 'dd')

回答6:

Select Replicate('0',2 - DataLength(Convert(VarChar(2),DatePart(DAY, GetDate()))) + Convert(VarChar(2),DatePart(DAY, GetDate())

Far neater, he says after removing tongue from cheek.

Usually when you have to start doing this sort of thing in SQL, you need switch from can I, to should I.

回答7:

Roll your own method

This is a generic approach for left padding anything. The concept is to use REPLICATE to create a version which is nothing but the padded value. Then concatenate it with the actual value, using a isnull/coalesce call if the data is NULLable. You now have a string that is double the target size to exactly the target length or somewhere in between. Now simply sheer off the N right-most characters and you have a left padded string.

SELECT RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DAY, '2012-12-09') AS varchar(2)), 2) AS leftpadded_day

Go native

The CONVERT function offers various methods for obtaining pre-formatted dates. Format 103 specifies dd which means leading zero preserved so all that one needs to do is slice out the first 2 characters.

SELECT CONVERT(char(2), CAST('2012-12-09' AS datetime), 103) AS convert_day

回答8:

SELECT RIGHT('0' 
             + CONVERT(VARCHAR(2), Month( column_name )), 2) 
FROM   table 

回答9:

select right('0000' + cast(datepart(year, GETDATE()) as varchar(4)), 4) + '-'+ + right('00' + cast(datepart(month, GETDATE()) as varchar(2)), 2) + '-'+ + right('00' + cast(datepart(day, getdate()) as varchar(2)), 2) as YearMonthDay