CODEWITHSUNDEEP
oracle-databaseplsql
zowie
zowie

Reputation: 51

Generate a random date on specific year

How can i generate a random date with the year "1900" in PL/SQL ?

For example, i want to generate in a variabile called date_random, dates that contain the year 1900 .

I tried in many ways (DATEADD,DATEDIFF) but can't get to the result. Some help, please ?

Upvotes: 0

Views: 718

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Give this a try too. Pure SQL tweak . Hope this helps.

SELECT TRUNC(to_date('01/01/'
  ||'&enter_year','mm/dd/yyyy'),'month')+ROUND(dbms_random.value(1,
  CASE
    WHEN mod('&enter_year',4) = 0
    THEN 366
    ELSE 365
  END)) days
FROM dual;

Upvotes: 0

Amna Hashmi
Amna Hashmi

Reputation: 40

Something like this may work? 

declare @FromDate date = '1900-01-01'
declare @ToDate date = '1900-12-31'

select dateadd(day, 
           rand(checksum(newid()))*(1+datediff(day, @FromDate, @ToDate)), 
           @FromDate)

Or perhaps something more like:

SELECT DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 365), '1900-01-01')
FROM your_table

This generates dates starting from 1900-01-01, and you can change the amount of days in the modulus value, I put 365 (1 year), this approach doesn't overflow.

Upvotes: 0

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

Give this a try:

DECLARE
   l_date   DATE;
BEGIN
   l_date := TRUNC (SYSDATE, 'YYYY') + 
             ROUND (DBMS_RANDOM.VALUE (1, 365));
   DBMS_OUTPUT.put_line (l_date);

   /* And for a specific year */
   l_date := TRUNC (DATE '1900-01-01', 'YYYY') + 
             ROUND (DBMS_RANDOM.VALUE (1, 365));
   DBMS_OUTPUT.put_line (l_date);
END;
/

The DBMS_RANDOM comes in really handy!

Upvotes: 3

Related Questions