How do I get the month and day with leading 0's in SQL? (e.g. 9 => 09)
SqlTsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 2 - Sql
For SQL Server 2012 and up , with leading zeroes:
SELECT FORMAT(GETDATE(),'MM')
without:
SELECT MONTH(GETDATE())
Solution 3 - Sql
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 - Sql
Try this :
SELECT CONVERT(varchar(2), GETDATE(), 101)
Solution 5 - Sql
Leading 0 day
SELECT FORMAT(GetDate(), 'dd')
Solution 6 - Sql
SQL Server 2012+ (for both month and day):
SELECT FORMAT(GetDate(),'MMdd')
If you decide you want the year too, use:
SELECT FORMAT(GetDate(),'yyyyMMdd')
Solution 7 - Sql
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.
Solution 8 - Sql
SELECT RIGHT('0'
+ CONVERT(VARCHAR(2), Month( column_name )), 2)
FROM table
Solution 9 - Sql
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
Solution 10 - Sql
DECLARE @day CHAR(2)
SET @day = right('0'+ cast(day(getdate())as nvarchar(2)),2)
print @day
Solution 11 - Sql
use
CONVERT(CHAR(2), DATE_COLUMN, 101)
to get the month part with 2 characters and
CONVERT(CHAR(2), DATE_COLUMN, 103)
for the day part.
Solution 12 - Sql
Might I suggest this user defined function if this what you are going for:
CREATE FUNCTION dbo.date_code (@my_date date) RETURNS INT
BEGIN;
DECLARE @retval int;
SELECT @retval = CAST(CAST(datepart(year,@my_date) AS nvarchar(4))
+ CONVERT(CHAR(2),@my_date, 101)
+ CONVERT(CHAR(2),@my_date, 103) AS int);
RETURN @retval;
END
go
To call it:
SELECT dbo.date_code(getdate())
It returns as of today
> 20211129
Solution 13 - Sql
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