如何在 SQL 中获得前导 0 的月份和日期？（例如 9 => 09）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13804096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the month and day with leading 0's in SQL? (e.g. 9 => 09)
提问by RJ.
DECLARE @day CHAR(2)
SET @day = DATEPART(DAY, GETDATE())
PRINT @day
If today was the 9th of December, the above would print "9".
如果今天是 12 月 9 日，则上面将打印“9”。
I want to print "09". How do I go about doing this?
我想打印“09”。我该怎么做？
回答by twoleggedhorse
Pad it with 00 and take the right 2:
用 00 填充它并取右边的 2：
DECLARE @day CHAR(2)
SET @day = RIGHT('00' + CONVERT(NVARCHAR(2), DATEPART(DAY, GETDATE())), 2)
print @day
回答by Michael Dawson
Use SQL Server's date styles to pre-format your date values.
使用 SQL Server 的日期样式预先设置日期值的格式。
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
回答by Sajjan Sarkar
For SQL Server 2012 and up , with leading zeroes:
对于 SQL Server 2012 及更高版本，带有前导零：
SELECT FORMAT(GETDATE(),'MM')
without:
没有：
SELECT MONTH(GETDATE())
回答by Andrew
Try this :
尝试这个 ：
SELECT CONVERT(varchar(2), GETDATE(), 101)
回答by Chris
Leading 0 day
领先0天
SELECT FORMAT(GetDate(), 'dd')
回答by Tony Hopkinson
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.
通常，当您必须开始在 SQL 中执行此类操作时，您需要从可以 I 切换到应该 I。
回答by billinkc
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.
这是左填充任何东西的通用方法。这个概念是使用 REPLICATE 创建一个版本，它只是填充值。然后将它与实际值连接起来，如果数据是可空的，则使用 isnull/coalesce 调用。您现在有一个字符串，它是目标大小的两倍，正好是目标长度或介于两者之间。现在简单地去掉最右边的 N 个字符，你就有了一个左边的填充字符串。
SELECT RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DAY, '2012-12-09') AS varchar(2)), 2) AS leftpadded_day
Go native
本土化
The CONVERTfunction offers various methods for obtaining pre-formatted dates. Format 103 specifies ddwhich means leading zero preserved so all that one needs to do is slice out the first 2 characters.
该CONVERT函数提供了获取预先格式化日期的各种方法。格式 103 指定dd这意味着保留前导零，因此需要做的就是切出前 2 个字符。
SELECT CONVERT(char(2), CAST('2012-12-09' AS datetime), 103) AS convert_day
回答by Sham Sunder
DECLARE @day CHAR(2)
SET @day = right('0'+ cast(day(getdate())as nvarchar(2)),2)
print @day
回答by Mark
SQL Server 2012+(for both month andday):
SQL Server 2012+（月和日）：
SELECT FORMAT(GetDate(),'MMdd')
If you decide you want the year too, use:
如果您决定也需要年份，请使用：
SELECT FORMAT(GetDate(),'yyyyMMdd')
回答by Dss
SELECT RIGHT('0'
+ CONVERT(VARCHAR(2), Month( column_name )), 2)
FROM table
