insert date and time [message #380293] |
Sat, 10 January 2009 04:15  |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
CREATE TABLE TEST (initial_time date , final_time date)
Is it possible to insert only time and not the date together with time in the above columns.Please put some light on this.
|
|
|
Re: insert date and time [message #380294 is a reply to message #380293] |
Sat, 10 January 2009 04:33   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
No, it's not possible.
It wouldn't make any sense anyway. If you had 09:00 in one field, and 09:05 in the other, you wouldn't know if there were five minutes apart, or one day and five minutes, or seven years and five minutes.
|
|
|
Re: insert date and time [message #380295 is a reply to message #380293] |
Sat, 10 January 2009 04:41   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
But if i need to run a stored procedure which should only run between a particular time than i can take the initial and final time and compare it with it.A select statement in the stored procedure and comparing system time with the time from the columns and if current time is between fetched time so it proceeds further.
|
|
|
Re: insert date and time [message #380296 is a reply to message #380295] |
Sat, 10 January 2009 04:58   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Then you would still have some problem when the initial_time is 23:00 and the final_time is 01:00 and you try to run at 00:10, since then the runtime would not be between those two times.
So there is still some logic to be figured out. Maybe store initial_time with a fixed date.
Then store final_time with the same fixed date if it's on the same day, and with the next date if it's on the next day.
You can of course get only the time part of a date by selecting
select sysdate - trunc(sysdate) from dual;
select initial_time - trunc(initial_time) from dual;
select final_time - trunc(initial_time) from dual;
for example.
which should give you three numbers where you can figure out if the first one is between the second two, with one special case where you have to add "1" to the sysdate when it's after 00:00 and initial_time and final_time are on different dates.
|
|
|
Re: insert date and time [message #380298 is a reply to message #380293] |
Sat, 10 January 2009 06:48   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
Ok Thomas will work on that.
I thought the task is easy so gave info like that...
But actually my requirement is that in the table :-
initial_time should have 00:00:00
final_time should have 06:00:00
Now in a stored procedure , i will select above two values and compare it (suppose in if condition ) with current time and if the time is between the two fetched values than proceed with further operation.Please tell me how to proceed.My main concern is to compare the current time with above two values.
Thanks in advance.
|
|
|
|
Re: insert date and time [message #380419 is a reply to message #380293] |
Sun, 11 January 2009 22:07   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
hi Michel,
Main is problem is to store time in two columns
initial_time and final_time. What data type do i need to take ? Or how can i store just time in the above columns?
|
|
|
|
Re: insert date and time [message #380431 is a reply to message #380293] |
Sun, 11 January 2009 22:56   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
I think you not read the above posts , where i have given the inputs and my requirement too.
But i will try to put all things again.
CREATE TABLE TEST (initial_time date , final_time date)
insert into NPPI_PARAM_BOOT(initial_time,final_time)
values (TO_DATE('00:00:00','HH24:MI:SS'),TO_DATE('06:00:00','HH24:MI:SS') )
Now in the database
initial_time is 1/1/2009
final_time is 1/1/2009 6:00:00 AM
Now what i need here is to have an (in a stored procedure)if condition to compare the current time and check whether current time is in between 00:00:00 and 06:00:00, main concern is on how the if condition can be written, So please suggest me what i need to do here.
Thanks in advance.
[Updated on: Mon, 12 January 2009 00:54] by Moderator Report message to a moderator
|
|
|
|
Re: insert date and time [message #380463 is a reply to message #380293] |
Mon, 12 January 2009 00:24   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
I am able to achieve this now...
after creating table and inserting data using to_date and then in the procedure ...
CREATE OR REPLACE PROCEDURE TEST1
IS
initial_time date;
final_time date;
xx NUMBER;
yy NUMBER;
zz NUMBER;
BEGIN
select initial_time - trunc(initial_time) into xx from TEST_TABLE;
select sysdate - trunc(sysdate) into zz from dual;
select final_time - trunc(initial_time) into yy from TEST_TABLE;
IF zz BETWEEN xx AND yy
THEN
DBMS_OUTPUT.PUT_LINE ('Pass');
ELSE
DBMS_OUTPUT.PUT_LINE ('FAIL');
end if;
END;
[Mod-Edit: obfuscated table name as requested by poster]
[Updated on: Mon, 12 January 2009 01:01] by Moderator Report message to a moderator
|
|
|
|
|
|
|
Re: insert date and time [message #380475 is a reply to message #380293] |
Mon, 12 January 2009 00:50   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
For the group members one small info..
test table which i created is changed as NPPI_PARAM_BOOT.So there is change in the insert statement for the table.
|
|
|
|
|
|
Re: insert date and time [message #380484 is a reply to message #380293] |
Mon, 12 January 2009 01:03   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
Hi Blackswan
Quote: | You don't know what you don't know.
Depending upon implicit conversion, is a fools task.
Luck today could be disaster tomorrow!
|
Quote: | DATE is not NUMBER!
you are depending upon (implicit) conversion between different datatypes.
|
So i gave the output of statement which i use in the procedure.
If i am doing something wrong then guide me please , i dont know why are you getting irritated . sorry if i misunderstood you.
|
|
|
|
Re: insert date and time [message #380497 is a reply to message #380293] |
Mon, 12 January 2009 01:37   |
shaksing
Messages: 115 Registered: November 2008
|
Senior Member |
|
|
But if later somebody wants to change the initial and final time , I think this is the question Thomas asked me earlier. But i was not getting why the condition will fail..
Quote: | Then you would still have some problem when the initial_time is 23:00 and the final_time is 01:00 and you try to run at 00:10, since then the runtime would not be between those two times.
|
Please explain the above.
As far as the code which i posted is concerned that is working for values which i checked.Not sure will it cause problem for the pointed out by Thomas.
|
|
|
|
|