DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use the datename function?

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

*/

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE