Date Time Functions in SQL

Get Year
Select datepart(Year,getdate())
or
Select Year(getdate())


Get Month
Select datepart(Month,getdate())
or
Select Month(getdate())
Get Day

Select datepart(day,getdate())
or
Select Day(getdate())
Get Week Number
Select Datepart(Wk,getdate())
Get WeekDayName
Select DateName(dw,getdate())
(to get weeknumber DateName(Week,getdate()))
Get hours
select Datepart(hh ,getdate())
Get Minutes
Select Datepart(n ,getdate())
Get Seconds
Select Datepart(ss ,getdate())