OraFAQ Forum: Forms » DATE FORMAT

Home » Developer & Programmer » Forms » DATE FORMAT (Window 8 , Oracle data base 11g, Oracle Form 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DATE FORMAT [message #626147]

Tue, 21 October 2014 08:18

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Hi All
I am using oracle form 11g when i write on pre insert trigger -->
:PAS_POLUNDCOMMENT.AUDIT_OB_PREPARED_ON:=TO_date(SYSDATE,'DD-MM-RRRR HH24:MI:SS');

compile successfully

but
when i inserted record that is not saved in 'DD-MM-RRRR HH24:MI:SS'
saved in this format (21-10-2014). i need a save record in (21-10-2014 18:26:44) Format

sorry for weak English

Regard

Nasir Azeem.

Report message to a moderator

Re: DATE FORMAT [message #626149 is a reply to message #626147]

Tue, 21 October 2014 08:33

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

sysdate is a date. to_Date takes a string and converts it to a date.
So for to_date(sysdate) to work oracle first has to convert sysdate to a string.
So your code is really doing this:

:PAS_POLUNDCOMMENT.AUDIT_OB_PREPARED_ON:=TO_date(to_char(SYSDATE, <default date format of session>),'DD-MM-RRRR HH24:MI:SS');

If AUDIT_OB_PREPARED_ON is a date (and it should be) then you should simply set it to sysdate.
If it's a varchar then you should be using to_char not to_date.

Report message to a moderator

Re: DATE FORMAT [message #626165 is a reply to message #626149]

Tue, 21 October 2014 14:28

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Saying (in general) that

:PAS_POLUNDCOMMENT.AUDIT_OB_PREPARED_ON := sysdate;

suggests that an item contains date (+ time). I don't use Forms 11g, but I suppose it is similar to earlier versions. Therefore, check whether AUDIT_OB_PREPARED_ON's data type is DATETIME (instead of just DATE).

Report message to a moderator

Re: DATE FORMAT [message #626169 is a reply to message #626149]

Wed, 22 October 2014 00:19

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Dear
what do you mean by that?

<default date format of session>

Report message to a moderator

Re: DATE FORMAT [message #626171 is a reply to message #626169]

Wed, 22 October 2014 01:04

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

select * from nls_database_parameters;

will - among other parameters - show NLS_DATE_FORMAT.

But that doesn't really matter in your case. Just avoid TO_DATE against SYSDATE.

Report message to a moderator

Re: DATE FORMAT [message #626172 is a reply to message #626171]

Wed, 22 October 2014 01:30

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Thanks dear
my work have been done. in column property define format mask DD-MM-RRRR HH24:MI:SS and initial value defined $$DATE$$. data type datetime.
also on use pre insert trigger
:PAS_POLUNDCOMMENT.AUDIT_OB_PREPARED_ON:=TO_date(to_char(SYSDATE, 'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS');

so my work had done thanks for support me Razz

Regard

Nasir Azeem.

Report message to a moderator

Re: DATE FORMAT [message #626175 is a reply to message #626172]

Wed, 22 October 2014 02:33

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

*Sigh* - the fact that oracle is doing an implicit to_char doesn't mean that you need an explicit to_char.
this:

TO_date(to_char(SYSDATE, 'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS')

Is the same as this:

sysdate

Using to_date(to_char()) on a date is never necessary.

I suggest you read this: http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Report message to a moderator

Previous Topic:	Scrolling Employee Name Using (Timer) On Oracle Form
Next Topic:	user define message should populate

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 12:40:34 CDT 2024