How to get the current date  in SQL Server

List of commonly used data and time functions:
GETDATE()
DATEADD()
DATEPART()
DATEDIFF()
DATENAME()
DAY()
MONTH()
YEAR()

How to get the current data in SQL Server

GETDATE()
GETDATE() is very common used method which returns exact date time from the system. It does not used any parameter. Lets see as follows:

SELECT GETDATE()
--------------------------------
Aug 15 2009  9:04PM

How to add and subtract datatime in SQL Server

DATEADD()
DATEADD() is used to add or subtract datetime. Its return a new datetime based on the added or subtracted interval.

Declare @Date datetime 

set @Date = (SELECT GETDATE());
print  @Date -- Print Current Date
-- Adding 5 days with Current Date
SELECT DATEADD(day, 5,@Date ) AS NewTime
-----------------------------------------------------
Aug 15 2009  9:19PM
NewTime
-----------------------
2009-08-20 21:19:15.170

How to get the part like year, month and hour from the date in SQL Server

DATEPART()
Datepart is used when we need a part of date or time from a datetime variable. We can use DATEPART() method only with select command.

DATEPART(datepart, date)

SELECT DATEPART(year, GETDATE()) AS 'Year'
-- Get Only Month
SELECT DATEPART(month, GETDATE()) AS 'Month'
-- Get Only hour
SELECT DATEPART(hour, GETDATE()) AS 'Hour

How to do the difference between two data time

DATEDIFF()
Datediff is a function to find out the difference between two DateTime elements.

SELECT DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay

How to get the name of date time in SQL Server

DATENAME()
DATENAME() is a function to find out the date name from the datetime value.

-- Get Today
SELECT DATENAME(dw, getdate()) AS 'Today Is'
-- Get Mont name
SELECT DATENAME(month, getdate()) AS 'Month'

DAY()
DAY() is used to get the day from any date time object.

select day(getdate()) as 'DAY'


MONTH()
Month is a function used to get the month from the date time object.

SELECT MONTH(getdate()) AS 'Month'


YEAR()
Year is a function used to get the year from the date time object.

SELECT YEAR(getdate()) AS 'Year'