|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of the DATENAME date and time function.
-- SQL datename - SQL datepart - SQL dateformat
-- SQL Server datetime functions - SQL inner join - SQL between
USE AdventureWorks;
SET DATEFORMAT mdy
SELECT DATENAME(weekday, '2016-10-23')
-- Sunday
------------
SELECT DATENAME(month, '2016-10-23')
-- October
------------
SELECT DATENAME(year, '2016-10-23')
-- 2016
------------
GO
SELECT PC.Name AS ProdCat,
PS.Name AS SubCat,
DATEPART(yy,SOH.OrderDate) AS OrderYear,
'Q' + DATENAME(qq,SOH.OrderDate) AS OrderQtr,
-- SQL currency formatting - money format
'$' + convert(VARCHAR,SUM(SOD.UnitPrice * SOD.OrderQty),
1) AS SalesRev
FROM Production.ProductSubcategory PS
INNER JOIN Sales.SalesOrderHeader SOH
INNER JOIN Sales.SalesOrderDetail SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product P
ON SOD.ProductID = P.ProductID
ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (SOH.OrderDate BETWEEN '2/1/2002' AND '2/28/2003')
GROUP BY DATEPART(yy,SOH.OrderDate),
PC.Name,
PS.Name,
'Q' + DATENAME(qq,SOH.OrderDate),
PS.ProductSubcategoryID
ORDER BY ProdCat,
SubCat,
OrderYear,
OrderQtr
GO
/* Partial results
ProdCat SubCat OrderYear OrderQtr SalesRev
Accessories Helmets 2002 Q1 $4,360.28
Accessories Helmets 2002 Q2 $11,638.86
Accessories Helmets 2002 Q3 $33,853.10
Accessories Helmets 2002 Q4 $24,870.77
Accessories Helmets 2003 Q1 $7,731.43
Accessories Locks 2002 Q3 $6,325.00
*/
------------
|
