CODEWITHSUNDEEP
sqlms-access
rodrigocf
rodrigocf

Reputation: 2089

Select a random day from last week in Access SQL

I am attempting to build a query in Access that goes like this:

SELECT * 
FROM my_table
WHERE date_column BETWEEN X AND Y

Where I want X to be a random day of last week at 6am and Y the following day at 6am.

I know i can do:

WHERE date_column Between DateAdd('ww',DateDiff('ww',7,Date()),0)-6 And DateAdd('ww',DateDiff('ww',7,Date()),6)-6))

To get all last week of data, but how do I do it just for a random day last week?

Thanks!

Upvotes: 0

Views: 73

Answers (2)

Hank G
Hank G

Reputation: 36

DECLARE @x AS DATETIME
DECLARE @y AS DATETIME
DECLARE @randomday AS INT

SET @randomday = ABS(Checksum(NewID()) %6) 
SET @x = LEFT(CONVERT(nvarchar, DATEADD(DAY, -@randomday, GETDATE()), 120), 11) + N'06:00:00'
SET @y = DateAdd(day,1, @x)

SELECT * 
FROM my_table
WHERE date_column BETWEEN @x AND @y

Upvotes: 1

sarh
sarh

Reputation: 6627

In Access query you can use VBA functions, so this should give you what you need: DateAdd('ww',DateDiff('ww',7,Date()),0)-Round(6*Rnd())

The difference with your solution is that instead of -6 days you reduce it to Round(6*Rnd()). Rnd returns number >=0 and <1. Than you need to multiply it to max gap and round to get the integer.

Upvotes: 0

Related Questions