CODEWITHSUNDEEP
sql-serverdatereporting-servicestime
Quiroga
Quiroga

Reputation: 15

T-SQL: select date range from last Friday at 1230pm until Monday at 330am

I'm creating a query in SQL Server 2008, for an SSRS report that runs every Monday at 0330am, retrieving weekend sales details.

The report is ready, but I had to hard-code the date range, as I'm having trouble expressing that condition in the WHERE statement.

I need to retrieve data based on column [salestime] (of type datetime), between Friday at 1230pm and Monday at 330am.

I'd really appreciate your assistance with this.

Upvotes: 0

Views: 47

Answers (2)

BJones
BJones

Reputation: 2460

How about something like:

WHERE SalesTime BETWEEN DATEADD(HH,-63,GETDATE()) AND GETDATE()

I believe the time values are 63 hours apart. This is if your report automatically runs at 3:30 AM, which is what it sounds like in your post.

Upvotes: 1

Code Different
Code Different

Reputation: 93181

If you want to run your report any time during the week to report on last weekend's sale

SET DATEFIRST 7

DECLARE @ThisMonday     date        = DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), GETDATE())
DECLARE @LastFriday     date        = DATEADD(DAY, -3, @ThisMonday)

DECLARE @StartTime      datetime    = CAST(@LastFriday AS datetime) + CAST('12:30' AS datetime)
DECLARE @EndTime        datetime    = CAST(@ThisMonday AS datetime) + CAST('03:30' AS datetime)

SELECT  @ThisMonday, @LastFriday, @StartTime, @EndTime

Now you can filter your report with salestime BETWEEN @StartTime AND @EndTime.

Upvotes: 0

Related Questions