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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For SQL Server 2012 and up   with leading zeroes    SELECT FORMAT GETDATE    MM      without   SELECT    MONTH GETDATE

User · Answer

Try this     SELECT CONVERT varchar 2   GETDATE    101

User · Answer

Leading 0 day  SELECT FORMAT GetDate     dd

User · Answer

SELECT RIGHT  0                  CONVERT VARCHAR 2   Month  column name     2   FROM   table

User · Answer

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

User · Answer

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

User · Answer

DECLARE  day CHAR 2   SET  day   right  0   cast day getdate   as nvarchar 2   2   print  day

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

Roll your own method

Go native

Examples related to sql

Examples related to tsql