How to use the YEAR, MONTH and DAY functions?

How to use the YEAR, MONTH and DAY functions?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate the application of Year, Month and Day datetime functions.

 

-- T-SQL Year, Month & Day functions

DECLARE @Date datetime = '2018-03-15'

 

-- SQL Server Year function

SELECT YYYY=YEAR(@Date)

SELECT YYYY=DATEPART(yy,@Date)

-- 2018

 

-- SQL Server Month function

SELECT MM=MONTH(@Date)

SELECT MM=DATEPART(mm,@Date)

-- 3

 

-- SQL Server Day function

SELECT DD=DAY(@Date)

SELECT DD=DATEPART(dd,@Date)

-- 15

 

-- YYYYMM aggregation

SELECT YYYYMM=CONVERT(varchar,year(OrderDate))+

       RIGHT('0'+CONVERT(varchar,month(OrderDate)),2),

       MonthlySales=SUM(TotalDue)

FROM AdventureWorks2008.Sales.SalesOrderHeader

WHERE YEAR(OrderDate)>2001

GROUP BY CONVERT(varchar,year(OrderDate))+

         RIGHT('0'+CONVERT(varchar,month(OrderDate)),2)

ORDER BY YYYYMM Desc

/*    YYYYMM      MonthlySales

      200407      56178.9223

      200406      6728034.9873

      200405      6518825.2262

      200404      4722890.7352

      200403      5272786.8106

      200402      5207182.5122

      200401      3691013.2227

*/

 

-- Alternative solution

SELECT YYYYMM=CONVERT(char(6),OrderDate,112),

       MonthlySales=SUM(TotalDue)

FROM AdventureWorks2008.Sales.SalesOrderHeader

WHERE YEAR(OrderDate)>2001

GROUP BY CONVERT(char(6),OrderDate,112)

ORDER BY YYYYMM Desc

------------

Regards :

Vikram Singh Rana

Computer Science Engineer

9983384690