Home » SQL & PL/SQL » SQL & PL/SQL » Create random Date/and Time (9i)
Create random Date/and Time [message #337027] Tue, 29 July 2008 12:58 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I need to generate a random date/time stamp for entry into a field. I will also need to have this within certain time constraints but could wrap that into loop if need be unless there is a simple way to do this.

Thanks

Report message to a moderator

Re: Create random Date/and Time [message #337029 is a reply to message #337027] Tue, 29 July 2008 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68693
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
start_date + dbms_random.value(0,86400*(end_date-start_time)+1)/86400

Regards
Michel

Report message to a moderator

Re: Create random Date/and Time [message #337037 is a reply to message #337029] Tue, 29 July 2008 13:32 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Would you please elaborate, i tried a few things with it and could not get it to work the way needed. Lets say that I need a random date between july 30 at 12 am and august 3 at 9pm

Report message to a moderator

Re: Create random Date/and Time [message #337039 is a reply to message #337027] Tue, 29 July 2008 13:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

This problem really has NOTHING to do with Oracle & is simply a Programming 101 homework assignment.

Report message to a moderator

Re: Create random Date/and Time [message #337042 is a reply to message #337039] Tue, 29 July 2008 13:49 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Maybe you should read points 1 and 2 of the guidelines:

# Be polite!
# Never belittle anyone for asking beginner-level questions or for their English skills.

This is not a homework assignment, google nor yahoo searches have provided a good way to do this. My DBA is out of the office at the moment and I need to find a solution to meet a deadline. I have generated random numbers and random text strings in the past, however I need to now generate a random date and time stamp between 2 dates. It IS oracle related as the data will reside in oracle tables therefore must be populated using oracle syntax.

Report message to a moderator

Re: Create random Date/and Time [message #337045 is a reply to message #337037] Tue, 29 July 2008 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68693
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Replace start_date and end_date by your start date and end date.
Use TO_CHAR to translate your strings to Oracle dates.

Regards
Michel

Report message to a moderator

Re: Create random Date/and Time [message #337047 is a reply to message #337027] Tue, 29 July 2008 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle's date datatype has granularity of 0.1 seconds.
Most Random Number Generators (RNG) produce values between 0 & 1.
Compute the number of tenths of seconds within your interval.
Multiply the random values by appropriate power of ten to just encompass the number of tenths of seconds.
This gives you an offset starting from the beginning of the interval.
Use TO_DATE to produce desired datatype.

Report message to a moderator

Re: Create random Date/and Time [message #337057 is a reply to message #337027] Tue, 29 July 2008 15:15 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
biohazardbill wrote on Tue, 29 July 2008 13:58
I need to generate a random date/time stamp for entry into a field. I will also need to have this within certain time constraints but could wrap that into loop if need be unless there is a simple way to do this.



What would be the need for a random date and time value? This is an honest question as I cannot think of any myself.

Report message to a moderator

Re: Create random Date/and Time [message #337059 is a reply to message #337057] Tue, 29 July 2008 15:25 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
We have an application that distributes prizes. There is a quota for the amount of weekly prizes to become available, however when the new week begins we do not want all the prizes released at midnight the first day. Therefore we have a table that will release prizes through the week. We need to randomly generate dates and times for these to be released.

thanks to michel I got it to work...

select TO_DATE('07/30/08','mm/dd/yy') + dbms_random.value(0,86400*(to_date('3-Aug-2008', 'dd-mm-yyyy') - to_date('30-jul-2008', 'dd-mm-yyyy'))+1)/86400 from dual;

Report message to a moderator

Re: Create random Date/and Time [message #337060 is a reply to message #337027] Tue, 29 July 2008 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
biohazardbill wrote on Tue, 29 July 2008 10:58
I need to generate a random date/time stamp for entry into a field. I will also need to have this within certain time constraints but could wrap that into loop if need be unless there is a simple way to do this.

Thanks


SELECT startdate+45 FROM orders SAMPLE (1) where startdate between sysdate and sysdate-45;

Above will generate "random" dates up to 45 days into the future.

Place inside a LOOP to generate as many as required.

Report message to a moderator

Re: Create random Date/and Time [message #337108 is a reply to message #337059] Tue, 29 July 2008 22:58 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
biohazardbill wrote on Tue, 29 July 2008 22:25

select TO_DATE('07/30/08','mm/dd/yy') + dbms_random.value(0,86400*(to_date('3-Aug-2008', 'dd-mm-yyyy') - to_date('30-jul-2008', 'dd-mm-yyyy'))+1)/86400 from dual;


Your format-masks do not resemble the format of your strings.

Report message to a moderator

Previous Topic: Aggregate Window Functions Causes Full Table Scan
Next Topic: ORA-01403: no data found + During Trigger Execution.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Sep 26 22:35:50 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.