updating single data in datetime field

Yaaa

75 pts.

Tags:

D2K

D2K 6i

D2K Forms

Oracle Forms 6i

hi...i've a column with datatype 'Date' in sql table...it stores both date and time together like '21/04/2010 3:09:00 PM'. and I want to create a customize form where

1)both these enteries comes in different fields.. and

2)when i update through trigger only time field the date remains same in the table..that is it doesnot affect the date part in the database table...

plz help me with both these problems..its urgent...thanks in advance..

Software/Hardware used:
d2k,forms 6i

Asked: May 22, 2010 5:20 AM Last updated: May 31, 2010 8:39 PM

Answer Wiki

Last updated: May 24, 2010 2:58 PM GMT

Meandyou5,220 pts.
History
Contributors
- Ordered by most recent
- Meandyou5,220 pts.

Thanks. We'll let you know when a new response is added.

In Oracle a column of datatype DATE does indeed include both date and time. I do not know how (or why) you would put them in different columns.

When you are updating the column, be sure to update the ENTIRE column, both the date portion and the time portion.

The Oracle function TO_CHAR can be used to access the date portion and the time portion separately or even the DAY potion or the MONTH portion or …

<pre>
SELECT TO_CHAR(SYSDATE, ‘MM/DD/YYYY HH24:MI:SS’) FROM DUAL;
—
— TO_CHAR(SYSDATE,’MM
— ——————-
— 04/20/2004 08:19:54
</pre>

You might also want to take a look at TO_DATE function.

<pre>
SELECT ‘TO DATE’ , TO_DATE(’01/28/2002′,’MM/DD/YYYY’) FROM DUAL;
—
— ‘TODATE TO_DATE(‘
— ——- ———
— TO DATE 28-JAN-02
</pre>

Discuss This Question: 4 Replies

carlosdl May 24, 2010 1:45 PM GMT

More details are needed. Are you using a database block ? If so, is it based on a table or a query ? If it is based on a query, you could modify your SELECT statement and use this expression as source for your time item: "to_char(yourColumn,'hh24:mi:ss')". What exaclty do you mean by "when i update through trigger" ? How are you updating the table(s) ?

84,495 pointsBadges:

report
Yaaa May 31, 2010 7:00 AM GMT

I have not created any form yet...but form will be based on database table only... i want to update through forms the database table....but the problem is that table column is of datatype DATE...it stores both date and time together like '21/04/2010 3:09:00 PM' as told earlier... so how should i create the form which has both these date n time fields separate?? n i want to update also through this form the time field only in the database but it shouldnt affect the date part in the database....plz reply soon...

75 pointsBadges:

report
carlosdl May 31, 2010 8:37 PM GMT
-Create a database item for your date field (select DATETIME as the data type for the text item) and use any date-only format mask. -Create a non-database item for the time -In the 'POST-QUERY' trigger of the block, put some code like this:
```
	:your_block.your_time_item := to_char(:your_block.your_date_item,'hh24:mi:ss');
```
-In the 'WHEN-VALIDATE-ITEM' trigger of your time item, put some code like this:
```
Declare
	newDate date := to_Date(to_char(:your_block.your_date_item,'ddmmyyyy')||:your_block.your_time_item,'ddmmyyhh24:mi:ss');
Begin
	if :your_block.your_date_item != newDate then
		:your_block.your_date_item := newDate;
	end if;
End;
```
84,495 pointsBadges:
report
carlosdl May 31, 2010 8:39 PM GMT

You might want to set the UPDATE_ALLOWED property to 'NO' for the date item.

84,495 pointsBadges:

report