Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sundar.

Asked: July 21, 2018 - 1:49 am UTC

Last updated: July 25, 2018 - 12:47 am UTC

Version: Toad for Oracle 11.6

Viewed 10K+ times! This question is

You Asked

I need to pick one random date per month from July 2017 to June 2018. I have the below mentioned code which is only selecting them for entire 2017 Calendar year whereas I need it from July 2017 to June 2018. Any advise?

with tst as (
Select level as mnth from dual connect by level <= 12
)
select to_date(floor(DBMS_Random.Value(1,to_char(last_day(to_date(mnth||'-'||2017, 'mm-yyyy')), 'dd')))||'-'||mnth||'-'||2017, 'dd-mm-yyyy') t from tst

and Connor said...

Simply use the days between the two dates as the size of your random number range.

SQL> with t as
  2   ( select date '2017-06-01' start_date,
  3            date '2018-04-01' end_date
  4     from dual )
  5  select start_date +
  6           trunc(dbms_random.value(0,(end_date-start_date+1)))
  7  from t;

START_DAT
---------
23-JUL-17

SQL>
SQL> /

START_DAT
---------
18-DEC-17

SQL> /

START_DAT
---------
13-MAR-18

SQL> /

START_DAT
---------
24-OCT-17



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Expanding a bit

AndyP, July 24, 2018 - 6:15 am UTC

Just in case that wasn't quite what you were after, here's a possible approach. Lots of with clauses so you can follow the steps:
with data as
(
select date '2017-06-01' start_date
      ,date '2018-04-01' end_date
  from dual)
,ranges as
(
select start_date
      ,end_date
      ,months_between(end_date,start_date) + 1 months
  from data
)
,numbers as
(
select level l
  from ranges
connect by level <= months
)
,months as
(
select ranges.start_date
      ,add_months(ranges.start_date,numbers.l - 1) monthstart
      ,last_day(add_months(ranges.start_date,numbers.l - 1)) monthend
      ,last_day(add_months(ranges.start_date,numbers.l - 1)) - add_months(ranges.start_date,numbers.l - 1) + 1 days
  from numbers
      ,ranges
)
select monthstart + trunc(dbms_random.value(0,days)) randomdate
  from months
;

RANDOMDATE
--------------------
13-Jun-2017 00:00:00
30-Jul-2017 00:00:00
19-Aug-2017 00:00:00
02-Sep-2017 00:00:00
24-Oct-2017 00:00:00
23-Nov-2017 00:00:00
10-Dec-2017 00:00:00
02-Jan-2018 00:00:00
05-Feb-2018 00:00:00
14-Mar-2018 00:00:00
28-Apr-2018 00:00:00


Connor McDonald
July 25, 2018 - 12:47 am UTC

nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library