Home » Developer & Programmer » Forms » Date Format change During the Insert (Oracle 9i, Form 6i)
Date Format change During the Insert [message #400869] Thu, 30 April 2009 02:21 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

hi,

When i try to insert the records in to table. The insert are correctly done, but the date format are changed thus year is changed to 0009. How to solve this problem.

The Following are the code using:
Declare
Cntdt   Number(3);
begin

Declare
	Cursor emps is  SELECT (((TO_DATE('01-Apr-2009', 'DD/mm/YYYY') - 1)) + rownum) Prdate 
     FROM (SELECT 1 from DUAL 
     CONNECT BY level <= ((TO_DATE('30-Apr-2009', 'DD/mm/YYYY') + 1) - TO_DATE('01-Apr-2009', 'DD/MM/YYYY')));
	
Emp   Emps%Rowtype;

Begin
	Open Emps;
	Loop
		Fetch Emps into emp;
		Exit when Emps%NotFound;
	
	Declare
		Cursor empl is select emcode,unmast,departcode from emmast 
		             where unmast = 2;
		               
		               
	Empt empl%Rowtype;
	WeekD Char(10);
	
	Begin
		Open Empl;
		Loop
			Fetch Empl into empt;
			Exit When Empl%NotFound;
			
			Select Upper(to_Char(To_date(Emp.Prdate,'dd/mm/yyyy'),'Day')) into WeekD from dual;
		
	If WeekD not in ('SUNDAY') Then
		Insert into attend Values (Empt.emcode,Emp.Prdate,'XX',0,null,Empt.departcode,Empt.unmast);
	Else
		Insert into attend Values (Empt.emcode,Emp.Prdate,'WW',0,null,Empt.departcode,Empt.unmast);
	End if;
	
		
		
		End Loop;
		Close Empl;
	End;
	End Loop;
	Close Emps;
End;
     Commit;
  
end;



Regards,

C V S
Re: Date Format change During the Insert [message #400879 is a reply to message #400869] Thu, 30 April 2009 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which column are we talking about and is its datatype DATE?

You should always list the columns you're inserting into in an insert statement.
Re: Date Format change During the Insert [message #400895 is a reply to message #400879] Thu, 30 April 2009 05:14 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Prdate is the date type which want to insert.


Thanks & Regards,

C V S
Re: Date Format change During the Insert [message #400896 is a reply to message #400869] Thu, 30 April 2009 05:21 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
And is the column (which you haven't named in the code) that you're inserting Emp.Prdate into on the attend table a date?
Re: Date Format change During the Insert [message #400897 is a reply to message #400896] Thu, 30 April 2009 05:24 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Yes.

Regards,

C V S
Re: Date Format change During the Insert [message #400899 is a reply to message #400869] Thu, 30 April 2009 05:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then I have no idea what the problem is.
Your cursor will find dates in the year 2009.
emp.prdate should be a date.
If the column you're inserting into is also a date then no conversion should occur.

How are you checking the value after it's been inserted?
Re: Date Format change During the Insert [message #400900 is a reply to message #400897] Thu, 30 April 2009 05:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If "emp.prdate" is of a DATE datatype, why do you convert it to DATE again?
Quote:
to_Char(To_date(Emp.Prdate,'dd/mm/yyyy'),'Day')



Would you mind to post code which contains modified INSERT statement, so that it includes column list; for example
INSERT INTO your_table (id, name, date_column)
VALUES (emp.id, emp.name, emp.date_col)


Also, it wouldn't hurt if you post tables' description (so that we could track datatypes).
Re: Date Format change During the Insert [message #400915 is a reply to message #400899] Thu, 30 April 2009 06:15 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

When I select the data after the Insert.

select * from attend where  attend_dt between '01-Apr-2009' and '30-APR-2009';

The Output is no rows selected

Re_try to insert the table, they were display the constraints error.

Thus i try the query of

select * from attend where  attend_dt between '01-Apr-0009' and '30-APR-0009';


It display the record.




Re: Date Format change During the Insert [message #400916 is a reply to message #400915] Thu, 30 April 2009 06:26 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If "attend_dt" is DATE, then you need to use TO_DATE with appropriate date mask:
SELECT * FROM attend
WHERE attend_dt BETWEEN TO_DATE('01-apr-2009', 'dd-mon-yyyy') AND ...
Because, the way you've written the query, you are comparing DATE with a STRING.

By the way, what about answers to my previous questions?
Re: Date Format change During the Insert [message #400917 is a reply to message #400869] Thu, 30 April 2009 06:27 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
knowing what your nls_date_format is would also be usefull.
Re: Date Format change During the Insert [message #401221 is a reply to message #400917] Sat, 02 May 2009 14:59 Go to previous messageGo to next message
sankaresh
Messages: 2
Registered: May 2009
Location: chennai
Junior Member

you can use this coding in pre-form trigger


set_application_property(PLSQL_DATE_FORMAT,'DD-MON-YYYY HH:MI:SS24');
(if u dont need time remove it.)
set_application_property(BUILTIN_DATE_FORMAT,'DD-MON-YYYY HH:MI:SS24');

forms_ddl('alter session set NLS_DATE_FORMAT = ''DD-MON-YYYY HH:MI:SS ''');


regards
Sankar
Re: Date Format change During the Insert [message #401308 is a reply to message #400869] Mon, 04 May 2009 00:46 Go to previous messageGo to next message
sasipalarivattom
Messages: 118
Registered: June 2007
Location: Cochin ( INDIA )
Senior Member

Dear friend,

Try using the format mask 'DD/mm/RRRR'.
It works in my system.

And I hope any expert will give us a comment on the difference between these two format masks.


Regards,
Sasi...

[Updated on: Mon, 04 May 2009 01:02]

Report message to a moderator

Re: Date Format change During the Insert [message #401491 is a reply to message #401221] Tue, 05 May 2009 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
sankaresh wrote on Sat, 02 May 2009 20:59
you can use this coding in pre-form trigger


set_application_property(PLSQL_DATE_FORMAT,'DD-MON-YYYY HH:MI:SS24');
(if u dont need time remove it.)
set_application_property(BUILTIN_DATE_FORMAT,'DD-MON-YYYY HH:MI:SS24');

forms_ddl('alter session set NLS_DATE_FORMAT = ''DD-MON-YYYY HH:MI:SS ''');


regards
Sankar


Alternatively the OP could find out where the dodgy conversion is happening and fix it - which'd be far more certain than messing about with system wide settings like that.

sasipalarivattom wrote on Mon, 04 May 2009 06:46

Dear friend,

Try using the format mask 'DD/mm/RRRR'.
It works in my system.

And I hope any expert will give us a comment on the difference between these two format masks.



It works for you but you're not sure what the difference is?
And you're recommending it anyway?

The RR Datetime Format Element
Re: Date Format change During the Insert [message #401861 is a reply to message #401491] Wed, 06 May 2009 13:05 Go to previous messageGo to next message
sankaresh
Messages: 2
Registered: May 2009
Location: chennai
Junior Member

hi friends

i used to test with format mask setting in property palette as DD-MM-RRRR
for the particular date field, but i used to get only with 0009
in the backend.


when i used to place the set_application_property ('plsql_date_format, 'DD-MM-YYYY' ) i am able to get as 2009
and am sure it gets the result only with plsql date format.

and we dont need to set the other properties like builtin date format and nls date format.

[Updated on: Wed, 06 May 2009 13:15]

Report message to a moderator

Re: Date Format change During the Insert [message #401952 is a reply to message #401861] Thu, 07 May 2009 02:34 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Does your last post mean that you are not going to set the date format via your program (with which I agree) and that your problem has been solved?

David
Previous Topic: List Item Problem
Next Topic: FIND ip ADDRESS OF PC
Goto Forum:
  


Current Time: Fri Dec 09 11:30:21 CST 2016

Total time taken to generate the page: 0.08220 seconds