CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user3482527
user3482527

Reputation: 247

Filtering data from the previous day using DateTime values

I am trying to retrieve data for yesterday's shift using SQL Server 2008 r2.

The shift starts at 20:00 pm, and ends 10:00 am following day.

What date function can I use to retrieve data for the entire shift?

Using this:

CAST([LastStartedDate] as time)>  cast('20:00' as Time)

Will only retrieve data up to midnight.

Upvotes: 1

Views: 2133

Answers (1)

Tanner
Tanner

Reputation: 22733

You can filter your dates with a WHERE clause that uses some date manipulation like so:

WHERE   LastStartedDate >= DATEADD(HOUR, 20,
                           CAST(CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS DATETIME));

This first gets yesterdays date, as a DATE so it removes the time portion:

SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
-- 2017-03-06

Then converts it back to a DATETIME to add the time as midnight of that day:

SELECT CAST(CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS DATETIME)
-- 2017-03-06 00:00:00.000

Then it adds 20 hours to get you to 20:00 - 8.00PM:

SELECT DATEADD(HOUR, 20,CAST(CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS DATETIME));
-- 2017-03-06 20:00:00.000

For your scenario, you need to do the same again to get the 10am cut off too and use BETWEEN.

Example:

CREATE TABLE #shift
    (
      LastStartedDate DATETIME ,
      WorkItemsDone INT
    );

INSERT  INTO #shift
        ( LastStartedDate, WorkItemsDone )
VALUES  ( DATEADD(HOUR, -18, GETDATE()), 10 ),
        ( DATEADD(HOUR, -15, GETDATE()), 20 ),
        ( DATEADD(HOUR, -14, GETDATE()), 30 ),
        ( DATEADD(HOUR, -10, GETDATE()), 40 ),
        ( DATEADD(HOUR, -5, GETDATE()), 25 ),
        ( DATEADD(HOUR, -2, GETDATE()), 15 ),
        ( DATEADD(HOUR, 4, GETDATE()), 5 ),
        ( DATEADD(HOUR, 10, GETDATE()), 15 );

-- all data
SELECT * 
FROM #shift

-- limited data
SELECT  *
FROM    #shift
WHERE   LastStartedDate BETWEEN 
        DATEADD(HOUR, 20, CAST(CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS DATETIME))
        AND DATEADD(HOUR, 10, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

DROP TABLE #shift;

Produces:

-- all data
LastStartedDate          WorkItemsDone
2017-03-06 16:18:04.877  10
2017-03-06 19:18:04.877  20
2017-03-06 20:18:04.877  30
2017-03-07 00:18:04.877  40
2017-03-07 05:18:04.877  25
2017-03-07 08:18:04.877  15
2017-03-07 14:18:04.877  5
2017-03-07 20:18:04.877  15

-- filtered data
LastStartedDate          WorkItemsDone
2017-03-06 20:18:04.877  30
2017-03-07 00:18:04.877  40
2017-03-07 05:18:04.877  25
2017-03-07 08:18:04.877  15

Upvotes: 2

Related Questions