SQL Server Date function

Current system date, time

select

2015-06-18 14:52:27.123
Extracting data from a date

day(), month(),year() 

Select GETDATE (); –2015-06-18 14:52:27.123
Select year (GETDATE ()); –2015
Select MONTH (getdate ()); –6
Select Day (getdate ()); –18

Date format Conversion
CONVERT

SELECTCONVERT(char(19), getdate(), 120)

Output format: 2008-02-27
Specific reference: MSDN

Date Plus and minus operations
Select GETDATE (); -2015-06-18 15:07:07.770
Select DATEADD (Year,-1,getdate ()); -2014-06-18 15:07:07.787
Operators and expressions

SQL Server Date function