SQL Server Portal

How to get day, month and year from Date Time? I came across this question many times in multiple blogs and there are many ways to do it in the earlier versions of SQL Server. In SQL Server 2012, you can also do it using FORMAT function (Method 3).
Given below are different methods to get day, month and year from date time which are compatible with different versions, including SQL Server 2012. Ensure that you are using correct method in your SQL Server version.

Method 1:

--This method will work on SQL SERVER 2005 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'

SELECT DATEPART(DAY, @Date_Time) as [Day],
DATEPART(MONTH, @Date_Time) as [Month],
DATEPART(YEAR, @Date_Time) as [Year]
--OUTPUT

Method 2:

--This method will work on SQL SERVER 2005 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'
SELECT DAY(@Date_Time) as [Day],
MONTH(@Date_Time) as [Month],
YEAR(@Date_Time) as [Year]
--OUTPUT

Method 3:

--This method will work on SQL SERVER 2012 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'
SELECT FORMAT(@Date_Time,'dd') as [Day],
FORMAT(@Date_Time,'MM') as [Month],
FORMAT(@Date_Time,'yyyy') as [Year]
--OUTPUT