While extracting specific portion from Date or from Date Time  we can use Date Part functions for doing that

-- Declare Variable

Declare @UserID;

--Extracting Day Month and Year

SELECT DATEPART(yyyy,UserCreated) AS UserYear,
DATEPART(mm,MonthofCreation) AS UserMonth,
DATEPART(dd,DayofCreation) AS UserDay
FROM Users
WHERE UserId=@UserID

Output:

10,10,2014

In this example the user day of creation its month of creation and its year of creation is extracted from date

Code             Meaning

year, yyyy, yy 	 Year interval
quarter, qq, q 	 Quarter interval
month, mm, m 	 Month interval
dayofyear 	 Day of year interval
day, dy, y 	 Day interval
week, dw, w 	 Week interval
weekday, ww, wk  Weekday interval
hour, hh 	 Hour interval
minute, mi, n 	 Minute interval
second, ss, s 	 Second interval
millisecond, ms  Millisecond interval