We help IT Professionals succeed at work.

Extract Month from Date field as a 2 character

I am trying to pull the month out of the "date_posted" field -- this is a normal date field -- but I want the months to be 2 characters
Jan = 01
Feb = 02 etc.

I cannot use a substring because the data is 1/18/2010 12:00 and so I'd pull 1/ for January and 12 for December. I've tried Month(date_posted), but that is only giving me one digits for early months.

Thanks
Comment
Watch Question

Top Expert 2010
Commented:
SELECT RIGHT('0' + CONVERT(varchar, MONTH(date_posted)), 2) AS MonthNumber
FROM SomeTable
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT CONVERT(char(2), date_posted, 1)

Author

Commented:
Sorry, this is a new ERP system, so we don't yet have data for Oct/Nov/Dec  -- so this works great for the April/May data -- is it going to make Oct into 01  or 010 ?-- because it is already a 2 digit month. This is one of those high profile reports (commissions), so I don't want to look any dumber in a few months, so figured I should ask the stupid qustions now.
Thanks
Top Expert 2010

Commented:
th1434,

Both of the above suggestions should work for any month of any year.

Patrick

Author

Commented:
I'm splitting the points -- hope that's okay -- since both work -- but matthewspatrick was quicker - although both were quick -- thanks!!
Top Expert 2010

Commented:
th1434,

Glad to help, and pleased to split.

Patrick