Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER 2012 – Get Day Month and Year separately from Date Time’

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

getdaymonthyear
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

getdaymonthyear1.1
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

getdaymonthyear1.2

Read Full Post »