Select a random date between two other dates SQL

Question

I need to select a number of records in MySQL between two dates randomly.

For example select 4 records between 30, between 2020-01-01, and 2020-06-01 randomly

Any idea how to do it?

share input vs expected output – Shoaeb May 08 '20 at 18:21 — Shoaeb, May 08 '20 at 18:21

score 2 · Accepted Answer · edited May 08 '20 at 18:53

2

You can order any result set randomly by using the RAND() function:

SELECT *
FROM `table`
WHERE date BETWEEN "2020-01-01" AND "2020-06-01" 
ORDER BY RAND()
LIMIT 4

edited May 08 '20 at 18:53

Strawberry

answered May 08 '20 at 18:23

2

why did u cast the date? – Shoaeb May 08 '20 at 18:26
1

@Shoaeb The variant without `CAST` will not check if a date is actually valid. See https://stackoverflow.com/q/8076236/3340665 and https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=84cdc0c9c0e1b75356b0ed7d5425e889 for examples. – Ulrich Thomas Gabor May 08 '20 at 19:50

1 Answers1