Tuesday, January 27, 2015

SELECT dữ liệu theo ngày tháng , thời gian .

-- BETWEEN dates implementation for datetime OrderDate - QUICK SYNTAX
DECLARE @StartDate datetime ='20040201', @EndDate datetime = '20040205';
SELECT OrderCount=COUNT(*) FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate and OrderDate < DATEADD(DD,1,@EndDate);
-- 502
-- BETWEEN dates implementation for datetime OrderDate using DATE data type
DECLARE @StartDate date ='20040201', @EndDate date = '20040205';
SELECT OrderCount=COUNT(*) FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate and OrderDate < DATEADD(DD,1,@EndDate);
-- 502

------------
-- BETWEEN datetime range QUICK SYNTAX
-- NOTE: equal operator would miss orders dated after midnight (on purpose or by mistake)
------------
-- Past midnight and next midnight
SELECT dateadd(dd, datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0),
       dateadd(dd, 1+datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0)
-- 2016-10-23 00:00:00.000    2016-10-24 00:00:00.000

-- Orders for today (daterange 1 day)
SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >= dateadd(dd, datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0)
  AND OrderDate <  dateadd(dd, 1+datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0)
------------

------------
-- SQL Server count business days function / UDF - exclude Saturdays & Sundays
------------
CREATE FUNCTION ufnGetBusinessDays (@DateStart DATETIME,
                                    @DateEnd   DATETIME)
RETURNS INT
AS
  BEGIN
      IF ( @DateStart IS NULL
            OR @DateEnd IS NULL )
        RETURN ( 0 )

      DECLARE @i INT = 0;

      WHILE ( @DateStart <= @DateEnd )
        BEGIN
            SET @i = @i + CASE
                            WHEN datename(dw, @DateStart) IN (
                                 'Saturday', 'Sunday' )
                          THEN 0
                            ELSE 1
                          END
            SET @DateStart = @DateStart + 1
        END -- while  
      RETURN ( @i )
  END -- function
GO

SELECT dbo.ufnGetBusinessDays('2016-01-01', '2016-12-31')
-- 261
------------
------------
-- SQL datetime between examples using copy of Sales.SalesOrderHeader
------------
USE tempdb;
SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader
GO

-- Sales order count for the entire month of MARCH 2004
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-03-01  00:00:00.000'
                           AND OrderDate < '2004-04-01  00:00:00.000'                    
-- 2109

-- Equivalent datetime or date comparison queries
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-03-01'
                           AND OrderDate < '2004-04-01'

-- SQL date between - new in SQL Server 2008 - SQL date range between
SELECT COUNT(*) FROM SOH WHERE
CAST(OrderDate AS DATE) BETWEEN '2004-03-01' AND '2004-03-31'

-- SQL datetime between with explicit inclusive lower and upper limits
/***** WORKS BUT NOT BEST PRACTICES FOR ENTIRE DAYS *****/  
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-31 23:59:59.997'

-- The following two queries work ONLY if all TIME parts are 00:00:00.000
/***** NOT BEST PRACTICES - WRONG RESULT IF TIME PART IS NOT 12:00AM *****/  
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-31 00:00:00.000'
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-03-01' AND '2004-03-31'
-- datetime range / date range example for SQL between
-- TIME PART assumed to be 12:00AM = 00:00:00.000 not specified
-- SQL datetime between - SQL datetime comparison
SELECT COUNT(*) FROM SOH WHERE OrderDate BETWEEN '2004-01-02' AND '2004-01-07'
-- 344

-- Equivalent datetime range query
-- SQL between is inclusive operator - it includes the limits
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-01-02 00:00:00.000' AND '2004-01-07 00:00:00.000'
-- 344

-- Create an order 1 sec passed midnight on 2004-01-07
-- SQL datetime functions
UPDATE TOP(1) SOH SET OrderDate=DATEADD(ss, 1, OrderDate)
       WHERE OrderDate='2004-01-07'
-- (1 row(s) affected)

 -- The sales order with OrderDate = 2004-01-07 00:00:01.000 excluded   
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-01-02 00:00:00.000' AND '2004-01-07 00:00:00.000'
-- 343

-- To include the entire day of 2004-01-07 specify upper limit time
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-01-02 00:00:00.000' AND '2004-01-07 23:59:59.997'
-- 344

-- Alternate formulation with >=...AND...< double datetime comparison
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-01-02'
                           AND OrderDate < '2004-01-08'
-- 344

-- The new DATE type will work correctly with the between operator
-- SQL date between
SELECT COUNT(*) FROM SOH WHERE
CAST(OrderDate AS DATE) BETWEEN '2004-01-02' AND '2004-01-07'
-- 344

-- Equivalent datetime comparison query
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-01-02  00:00:00.000'
                           AND OrderDate < '2004-01-08  00:00:00.000'
-- 344

-- Cleanup
DROP TABLE tempdb.dbo.SOH
GO
------------
------------
-- SQL datetime between T-SQL scripts using copy of HumanResources.EmployeePayHistory
------------
USE tempdb;
-- SQL select * into table
SELECT * INTO EPH
FROM AdventureWorks2008.HumanResources.EmployeePayHistory
GO
-- SQL select from new table - SQL datetime between - SQL date between
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE RateChangeDate BETWEEN '1997-11-01' AND '1998-01-05'
GO
/* Results

BusinessEntityID  RateChangeDate
3                 1997-12-12 00:00:00.000
4                 1998-01-05 00:00:00.000
*/

-- SQL update select into table
UPDATE EPH SET RateChangeDate = '1998-01-05 00:00:01.000'
WHERE BusinessEntityID = 4
  AND RateChangeDate = '1998-01-05'
GO

-- SQL between for DATE type
-- The following solution will NOT work correctly
-- The tacit assumption of TIME = 00.00.00.000 is no longer valid
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE RateChangeDate BETWEEN '1997-11-01' AND '1998-01-05'
GO
/* Results

BusinessEntityID  RateChangeDate
3                 1997-12-12 00:00:00.000
*/

-- The following solution will work correctly
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE RateChangeDate >= '1997-11-01' AND 
      RateChangeDate < DATEADD(dd,1,'1998-01-05')
GO
/* Results

BusinessEntityID  RateChangeDate
3                 1997-12-12 00:00:00.000
4                 1998-01-05 00:00:01.000
*/

-- Date type will work correctly - NOT SARGABLE
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE CONVERT(DATE,RateChangeDate) BETWEEN '1997-11-01' AND '1998-01-05'
GO

/* Results

BusinessEntityID  RateChangeDate
3                 1997-12-12 00:00:00.000
4                 1998-01-05 00:00:01.000
*/

-- Date only string type will also work correctly - NOT SARGABLE
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE CONVERT(char(10), RateChangeDate,102) BETWEEN '1997.11.01' AND '1998.01.05'
GO

/* Results

BusinessEntityID  RateChangeDate
3                 1997-12-12 00:00:00.000
4                 1998-01-05 00:00:01.000
*/


DROP TABLE tempdb.dbo.EPH
GO
----------
-- SQL date range between
----------
-- SQL between dates
USE AdventureWorks;
-- SQL between
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20040301' AND '20040315'
-- Result: 1112

-- BETWEEN operator is equivalent to >=...AND....<=
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-15  00:00:00.000'
/*
Sales orders with OrderDates

'2004-03-15  00:00:01.000'  - 1 second after midnight (12:00AM)
'2004-03-15  00:01:00.000'  - 1 minute after midnight
'2004-03-15  01:00:00.000'  - 1 hour after midnight

are not included in the two queries above.
*/
-- To include the entire day of 2004-03-15 use the following two solutions
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20040301' AND OrderDate < '20040316'

-- SQL between with DATE type (SQL Server 2008)- NOT SARGABLE
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE CONVERT(DATE, OrderDate) BETWEEN '20040301' AND '20040315'
-- 1112

-- SQL between with DATE type (SQL Server 2008)- SARGABLE
-- Same result accidentally because time part of OrderDate is 0
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20040301' AND '20040315'
-- 1112 

-- SQL range with DATE type (SQL Server 2008)- SARGABLE
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= CONVERT(DATE,'20040301') AND OrderDate < CONVERT(DATE,'20040316')
-- 1112 
------------

Source : sqlác

No comments:

Post a Comment