CODEWITHSUNDEEP
sqlt-sql
JJ.
JJ.

Reputation: 9960

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

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?

Upvotes: 76

Views: 206809

Answers (16)

Rolf
Rolf

Reputation: 7288

In Postgres:

SELECT TO_CHAR(your_timestamp_column, 'MM') FROM your_table;

Upvotes: 0

user22216763
user22216763

Reputation: 1

How about this?

SELECT CAST(CAST(GETDATE()AS DATE) AS NVARCHAR);
SELECT SUBSTRING(CAST(CAST(GETDATE()AS DATE) AS NVARCHAR),1,4);
SELECT SUBSTRING(CAST(CAST(GETDATE()AS DATE) AS NVARCHAR),6,2);
SELECT SUBSTRING(CAST(CAST(GETDATE()AS DATE) AS NVARCHAR),9,2);

Regards.

Upvotes: 0

Patrick DW
Patrick DW

Reputation: 1

Declare @dateToGet varchar(10)
Set @dateToGet = convert(varchar, getdate(), 112)

This works fine for the whole date with leading zeros in month and day

Upvotes: 0

Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2724

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

Upvotes: 1

Nkosinathi Mngomezulu
Nkosinathi Mngomezulu

Reputation: 1

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.

Upvotes: 0

Mark
Mark

Reputation: 235

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')

Upvotes: 4

Sham Sunder
Sham Sunder

Reputation: 1

DECLARE @day CHAR(2)

SET @day = right('0'+ cast(day(getdate())as nvarchar(2)),2)

print @day

Upvotes: 0

Chris
Chris

Reputation: 81

Leading 0 day

SELECT FORMAT(GetDate(), 'dd')

Upvotes: 8

Sajjan Sarkar
Sajjan Sarkar

Reputation: 4198

For SQL Server 2012 and up , with leading zeroes:

 SELECT FORMAT(GETDATE(),'MM')

without:

SELECT    MONTH(GETDATE())

Upvotes: 63

Sara
Sara

Reputation: 1

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

Upvotes: -1

Andrew
Andrew

Reputation: 101

Try this : 

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

Upvotes: 10

Dss
Dss

Reputation: 2370

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

Upvotes: 1

twoleggedhorse
twoleggedhorse

Reputation: 5048

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

Upvotes: 115

Michael Dawson
Michael Dawson

Reputation: 479

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

Upvotes: 25

billinkc
billinkc

Reputation: 61239

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

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

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.

Upvotes: 1

Related Questions