database 使用 DBMS_RANDOM 在 Oracle 中生成随机日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17449999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate a random date in Oracle with DBMS_RANDOM
提问by sharkbait
I have this anonymous block:
我有这个匿名块：
DECLARE
V_DATA DATE;
BEGIN
V_DATA := '01-GEN-2000';
HR.STATISTICHE.RATINGOPERATORI (V_DATA);
COMMIT;
END;
but I would to generate the date in a random way. How can I do?
但我会以随机方式生成日期。我能怎么做？
回答by Gaurav Soni
You can generate random dates between two dates ,as displayed in the query below .Random Dates are generated between 1-jan-2000 and 31-dec-9999
您可以在两个日期之间生成随机日期，如下面的查询所示。随机日期在 1-jan-2000 和 31-dec-9999 之间生成
SELECT TO_DATE(
TRUNC(
DBMS_RANDOM.VALUE(TO_CHAR(DATE '2000-01-01','J')
,TO_CHAR(DATE '9999-12-31','J')
)
),'J'
) FROM DUAL;
OR you can use
或者你可以使用
SELECT TO_DATE (
TRUNC (
DBMS_RANDOM.VALUE (2451545, 5373484)
)
, 'J'
)
FROM DUAL
In the above example ,the first value is 01-Jan-2000 and the second value id 31-dec-9999
在上面的例子中，第一个值是 01-Jan-2000，第二个值是 31-dec-9999
回答by Kamil M?trak
To generate random date you can use
要生成随机日期，您可以使用
select to_date('2010-01-01', 'yyyy-mm-dd')+trunc(dbms_random.value(1,1000)) from dual
or for random datetime
或随机日期时间
select to_date('2010-01-01', 'yyyy-mm-dd')+dbms_random.value(1,1000) from dual
回答by Cahid Topkaraoglu
If you want to see it's logic, you can also use this code.
如果你想看看它的逻辑，你也可以使用这段代码。
create or replace procedure genDate(result out nvarchar2) IS
year number;
month number;
day number;
Begin
year:=FLOOR(DBMS_RANDOM.value(2000,2100));
month:=FLOOR(DBMS_RANDOM.value(1,12));
IF month=2 and (year/4)=0 and (year/100)!=0 then
day:=FLOOR(DBMS_RANDOM.value(1,29));
ELSIF month=2 or (year/100)=0 then
day:=FLOOR(DBMS_RANDOM.value(1,28));
ELSIF MOD(month,2)=1 then
day:=FLOOR(DBMS_RANDOM.value(1,31));
ELSIF MOD(month,2)=0 and month!=2 then
day:=FLOOR(DBMS_RANDOM.value(1,30));
END IF;
result:=month||'-'||day||'-'||year;
End;
回答by AlexNikonov
here is one more option to generate date going back from now where 365 - days quanitity to move back from today, 'DD.MM.YYYY'- mask
这是生成从现在开始返回的日期的另一种选择，其中 365 - 从今天开始返回的天数，'DD.MM.YYYY'- 掩码
to_char(sysdate-dbms_random.value()*365, 'DD.MM.YYYY')
to_char(sysdate-dbms_random.value()*365, 'DD.MM.YYYY')
