[Solved] Extract month and day from a field in sql

Question

If your filed is a Date or DateTime, I would use the function DATEPART.

For example, this gets the current year:

DATEPART(year, GETDATE())

View the msdn page for the full documentation.

If your field is text, use CONVERT to convert your field to a DATE, then use the first method with the converted date as your value.

For example:

DATEPART(year, CONVERT(DATE, '11/1/2014'))

Complete Copy/Paste Example

DECLARE @DateVal VARCHAR(10) = '11/01/2014'
DECLARE @Month VARCHAR(2) = CAST(DATEPART(MONTH, CONVERT(DATE, @DateVal)) AS VARCHAR),
        @Day VARCHAR(2) = CAST(DATEPART(DAY, CONVERT(DATE, @DateVal)) AS VARCHAR)
PRINT REPLICATE('0', 2 - LEN(@Month)) + @Month + REPLICATE('0', 2 - LEN(@Day)) + @Day

Accepted Answer