SQL change format of Datepart Month

Question

How do you change the DATEPART format of the month to include the 0 in front of the month?

For instance 5 should be 05.

What I have tried, but none of it works, (only gives me 5)

Createdate = 2008-07-25 13:43:48.000

CONVERT(varchar(2), DATEPART(MONTH,(CreatedDate)))
CONVERT(varchar(2), DATEPART(MM,(CreatedDate)))

score 4 · Answer 1 · answered Nov 01 '12 at 07:02

4

I often use RIGHT as a cheap way to CONVERT.

select RIGHT(100+MONTH(CreatedDate),2)

answered Nov 01 '12 at 07:02

RichardTheKiwi

score 2 · Accepted Answer · answered Nov 01 '12 at 06:48

2

You can try

DECLARE @Createdate DATETIME = '2008-07-25 13:43:48.000'
SELECT SUBSTRING(CONVERT(VARCHAR(8), @Createdate, 3), 4, 2)

And here is an example

SQL Fiddle DEMO

answered Nov 01 '12 at 06:48

Adriaan Stander

Ah brilliant, Exactly that. Oh Okay i see how you did that. Awesome, thanks – Ruan Nov 01 '12 at 06:52

Vishal Suthar · Answer 3 · 2012-11-01T06:54:19.503

0

This is what you need

Createdate = 2008-07-25 13:43:48.000

Replace(str(datepart(mm, CreatedDate), 2),' ','0')

Output: 07

edited Nov 01 '12 at 06:54

answered Nov 01 '12 at 06:44

Vishal Suthar

uhm, are you sure? because i hover over MM and it says month in SQL server manager (and there is no mm, only MM) – Ruan Nov 01 '12 at 06:50