updating single data in datetime field

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: Last updated:
Related Questions
1

Answer Wiki

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

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    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,745 pointsBadges:
    report
  • Yaaa
    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
    -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,745 pointsBadges:
    report
  • carlosdl
    You might want to set the UPDATE_ALLOWED property to 'NO' for the date item.
    84,745 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: