Home » SQL & PL/SQL » SQL & PL/SQL » insert date and time (Oracle 9.2.0)
insert date and time [message #380293] Sat, 10 January 2009 04:15 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 #380308 is a reply to message #380298] Sat, 10 January 2009 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select * from mytable where sysdate bewteen initial_time and final_time;

Regards
Michel
Re: insert date and time [message #380419 is a reply to message #380293] Sun, 11 January 2009 22:07 Go to previous messageGo to next message
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 #380421 is a reply to message #380293] Sun, 11 January 2009 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>Main is problem is to store time in two columns
It appears only you think this is true.

>What data type do i need to take ?
DATE datatype

>Or how can i store just time in the above columns?
By (ab)using TO_DATE and/or TO_CHAR


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.
Re: insert date and time [message #380431 is a reply to message #380293] Sun, 11 January 2009 22:56 Go to previous messageGo to next message
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 #380432 is a reply to message #380293] Sun, 11 January 2009 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
How to you assign to a DATE variable just the time portion of SYSDATE?

There are multiple ways; including 1 already presented in this thread.
Re: insert date and time [message #380463 is a reply to message #380293] Mon, 12 January 2009 00:24 Go to previous messageGo to next message
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 #380466 is a reply to message #380463] Mon, 12 January 2009 00:31 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You don't know what you don't know.
Depending upon implicit conversion, is a fools task.
Luck today could be disaster tomorrow!

Re: insert date and time [message #380467 is a reply to message #380293] Mon, 12 January 2009 00:35 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi BlackSwan

Didn't get what you just said ? Is that relevant ?

Thanks for your help
Re: insert date and time [message #380468 is a reply to message #380293] Mon, 12 January 2009 00:41 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
DATE is not NUMBER!
you are depending upon (implicit) conversion between different datatypes.
Re: insert date and time [message #380470 is a reply to message #380293] Mon, 12 January 2009 00:44 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
select sysdate - trunc(sysdate) from dual;
gives output as 0.321736111111111...
Re: insert date and time [message #380475 is a reply to message #380293] Mon, 12 January 2009 00:50 Go to previous messageGo to next message
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 #380479 is a reply to message #380463] Mon, 12 January 2009 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't answer the most important question posted by Thomas:
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.

Regards
Michel
Re: insert date and time [message #380480 is a reply to message #380470] Mon, 12 January 2009 00:55 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
shaksing wrote on Sun, 11 January 2009 22:44
select sysdate - trunc(sysdate) from dual;
gives output as 0.321736111111111...



Are you bragging, complaining or what?
OK, your output exists.
what does it mean?

0.321736111111111 means what to who?
Re: insert date and time [message #380483 is a reply to message #380479] Mon, 12 January 2009 00:59 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Well Michel.

My initial and final time is fixed that is 00:00 and 06:00 respectively. Please let me know i address the correct thing here.
Re: insert date and time [message #380484 is a reply to message #380293] Mon, 12 January 2009 01:03 Go to previous messageGo to next message
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 #380496 is a reply to message #380483] Mon, 12 January 2009 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shaksing wrote on Mon, 12 January 2009 07:59
Well Michel.

My initial and final time is fixed that is 00:00 and 06:00 respectively. Please let me know i address the correct thing here.

If it is fixed you don't need any table with time, just check if current time is before 6AM.

Regards
Michel

Re: insert date and time [message #380497 is a reply to message #380293] Mon, 12 January 2009 01:37 Go to previous messageGo to next message
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.

Re: insert date and time [message #380500 is a reply to message #380497] Mon, 12 January 2009 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Current time: 00:10
Min time: 23:00 -> convert it in minutes for the example = 23*60 = 1380
Max time: 01:00 -> in minutes = 1*60 = 60

Can 10 (or anything) be greater than 1380 and less than 60?

Regards
Michel
Re: insert date and time [message #380502 is a reply to message #380293] Mon, 12 January 2009 01:55 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Ok Michel i got this one and it will cause a problem in case the initial and final time are changed as mentioned by Thomas.

So is there any generalized way to implement my requirement ? Please suggest me?

[Updated on: Mon, 12 January 2009 03:33]

Report message to a moderator

Previous Topic: SQL - Date group by
Next Topic: Duplicate rows
Goto Forum:
  


Current Time: Wed Dec 07 16:34:31 CST 2016

Total time taken to generate the page: 0.12077 seconds