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 construct correlated datetime query?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate a query with correlated OrderDate and DueDate ranges.


-- Period: 2004 MAR & APR - May 1st is not included, used as a limit
-- SQL inner join - SQL between dates - SQL between datetimes - SQL between two dates
DECLARE @PeriodStart datetime, @PeriodEnd datetime
SET @PeriodStart = '2004-03-01'
SET @PeriodEnd = '2004-05-01'
SELECT 'Due Date Period from '+CAST(DATEADD(DD,21,@PeriodStart) as varchar)+
' to '+ CAST(DATEADD(ms,-3, DATEADD(DD,21,@PeriodEnd)) as varchar)

-- Due Date Period from Mar 22 2004 12:00AM to May 21 2004 11:59PM
 
USE AdventureWorks;
SELECT OrderDate=convert(varchar,poh.OrderDate,101),
       DueDate = convert(varchar,pod.DueDate,101),
       poh.PurchaseOrderID,
       VendorName = v.[Name],
       ProductName = p.[Name],
       UnitPrice,
       Quantity = pod.OrderQty,
       pod.LineTotal,
       poh.VendorID,
       pod.ProductID
FROM   Purchasing.PurchaseOrderHeader poh
       INNER JOIN Purchasing.PurchaseOrderDetail pod
         ON poh.PurchaseOrderID = pod.PurchaseOrderID
       INNER JOIN Purchasing.Vendor v
         ON poh.VendorID = v.VendorID
       INNER JOIN Production.Product p
         ON pod.ProductID = p.ProductID
-- The BETWEEN operator would include @PeriodEnd as well
WHERE  poh.OrderDate >= @PeriodStart AND OrderDate < @PeriodEnd
       AND pod.DueDate >= DATEADD(DD,21,@PeriodStart)
       AND pod.DueDate < DATEADD(DD,21,@PeriodEnd)
ORDER BY PurchaseOrderID
GO
 
/* Partial results
 
OrderDate   DueDate     PurchaseOrderID   VendorName        ProductName
03/08/2004  03/22/2004  1885              Australia Bike…   Thin-Jam Lock Nut 11
03/08/2004  03/22/2004  1886              Beaumont Bikes    Chainring
03/08/2004  03/22/2004  1886              Beaumont Bikes    Chainring Bolts
03/08/2004  03/22/2004  1886              Beaumont Bikes    Chainring Nut
03/08/2004  03/22/2004  1887              Bergeron …        Lock Nut 16
03/08/2004  03/22/2004  1887              Bergeron …        Lock Nut 17

*/

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