CODEWITHSUNDEEP
c#sqlsql-server
user1438082
user1438082

Reputation: 2748

SQL For this shift

The query below gets data from 4am on the date the query is ran until now.

The problem is I need to also run the query at 2am on the following day (to return data from 4am on the previous day to now)- Hence the query no longer works as it is querying the wrong day - i.e I always want the query to have its start time at 4am on the current day unless the query is being ran between 12 am and 2 am - for which i want the start date to be 4am on the previous day.

I have the query in the application configuration file in my winform c# program. Without programmatically sending the day offset, is it possible to modify the query to work with my requirements ?

SELECT * 
FROM [TraceData].[dbo].[ActiveData2] 
where Timestamp  >= CAST(GETDATE()-0 AS DATE) + CAST('04:00:00' AS DATETIME)  
order by Timestamp DESC

Upvotes: 0

Views: 68

Answers (1)

TomP123
TomP123

Reputation: 38

This seems like what you're after:

SELECT * 
FROM [TraceData].[dbo].[ActiveData2] 
where Timestamp  >= CASE 
    WHEN CAST(CURRENT_TIMESTAMP AS TIME) BETWEEN CAST('00:00:00' AS TIME) AND CAST('02:00:00' AS TIME)
    THEN CAST(GETDATE()-0 AS DATE) + CAST('04:00:00' AS DATETIME) 
    ELSE CAST(GETDATE()-1 AS DATE) + CAST('04:00:00' AS DATETIME)
order by Timestamp DESC

However it looks like between 2am and 4am, you'll be trying to return data from the future. Not sure whether that's what you want...

Upvotes: 1

Related Questions