SQL SERVER -- Date Functions-- Part-17

                   By Using Date Functions we can extract different parts of dates like Year, Month and Day so.. on. And following are the date functions available in SQL Server.

GETDATE:-    Used to Display/Get system date.

-- GETDATE()

SELECT GETDATE() AS TODAY  

YEAR:-           Used to extract only year from the given date.

MONTH:-       Used to extract only Month from the given date.
DAY:-            Used to extract only Day from the given date.

SELECT GETDATE() AS SYSTEM_DATE

-- YEAR

SELECT YEAR(GETDATE()) AS YEAR

-- MONTH

SELECT MONTH(GETDATE()) AS MONTH

-- DAY

SELECT DAY(GETDATE()) AS DAY

DATEPART:-  Used to extract the part of the date from the       given date.

-- DATEPART

SELECT DATEPART(YY,GETDATE()) AS YEAR

SELECT DATEPART(MM,GETDATE()) AS MONTH

SELECT DATEPART(DD,GETDATE()) AS DAY

SELECT DATEPART(HH,GETDATE()) AS HOUR

SELECT DATEPART(DW,GETDATE()) AS DAY_OF_WEEK

SELECT DATEPART(WW,GETDATE()) AS WEEK_OF_YEAR

DATENAME:- Used to extract the relevant name of the part of the             given date.

-- DATENAME

SELECT DATENAME(YY,GETDATE()) AS YEAR

SELECT DATENAME(MM,GETDATE()) AS MONTH

SELECT DATENAME(DD,GETDATE()) AS DAY

SELECT DATENAME(HH,GETDATE()) AS HOUR

SELECT DATENAME(DW,GETDATE()) AS DAY_OF_WEEK

DATEDIFF:-  Used to Display/Get system date.

-- DATEDIFF

DECLARE @VALUE DATE

SET @VALUE='01-01-2013'

SELECT @VALUE

SELECT DATEDIFF(YY,@VALUE,GETDATE()) AS DIFF_IN_YEARS

SELECT DATEDIFF(MM,@VALUE,GETDATE())AS DIFF_IN_MONTHS

DIFFERENT FORMAT OF THE GIVEN DATES

SELECT GETDATE() AS SYSTEM_DATE

-- Different format of the given dates.

SELECT CONVERT(VARCHAR,GETDATE()) AS DEFAULT_FORMAT

SELECT CONVERT(VARCHAR,GETDATE(),100)

SELECT CONVERT(VARCHAR,GETDATE(),101) AS 'MM/DD/YYYY'

SELECT CONVERT(VARCHAR,GETDATE(),102) AS 'YYYY/MM/DD'

SELECT CONVERT(VARCHAR,GETDATE(),112) AS 'YYYY/MM/DD'

--

--

--

--

SELECT CONVERT(VARCHAR,GETDATE(),131) AS 'MM/DD/YYYY'