-
I am trying to fetch records when users query the database useing "MM-DD-YY" date format. The problem is with the format , Oracle recognozes "DD-MON-YY" format and users like "MM-DD-YY" date format, so how do I convert this? I
tried useing to_date(to_char('Pdate','MM-DD-YY'),'MM-DD-YY')
but its not working.
It either says"non numeric character was found where a numeric was expected ORA-01858"
OR "not a valid month"
All I need it users enter date in MM-DD-YY format I need to convert to DD-MOn-YY format as Oracel understands only one format:-(
-
Hmm, not really sure what you're getting at...
A date is a date is a date. How it is displayed is irrelevent. You can display it in any format you want:
Code:
SQL> select to_char(d,'mm/dd/yyyy') from xyz;
TO_CHAR(D,
----------
09/30/2002
10/30/2002
10/15/2002
SQL> select to_char(d,'mon-yyyy-d') from xyz;
TO_CHAR(D,
----------
sep-2002-2
oct-2002-4
oct-2002-3
SQL> select to_char(d,'mi:ss:hh month-yyyy-ddd') from xyz;
TO_CHAR(D,'MI:SS:HHMONTH-YY
---------------------------
29:06:01 september-2002-273
29:17:01 october -2002-303
29:27:01 october -2002-288
Jeff Hunter
-
This procedure get teh date the user enters(in format "MM-DD-YYYY") and brings all the recrds table1 and should insert into table2 present in diffrent database.
The date is passed as in parameter to the procedure.
PROCEDURE post_to_treasury (payin_dt in date,records_processed out number)
IS
id_Process_Date Date :=to_date(payin_Dt,'MM-DD-YY'); (DATE CONVERSION)
payin_line number:=0;
id_time_stamp varchar2(20);
seq_num number:=0;
cnt_rec number;
post_by varchar2(80);
cnt number;
cursor cur_post_payins is
select dept_payin_no,payin_line_no, payin_date, cr_dr_flag, payin_amount, post_flag,
gl_Fund_code, gl_org_code, gl_acct_code, gl_prog_code,payin_comment
from post_treasury
where post_flag='N'
and To_Date(to_char(Payin_Date,'MM-DD-YY'),'MM-DD-YY') =id_process_date
for update of post_flag,post_date, posted_by
order by payin_line_no;
BEGIN
Message('Processing, Please wait....');
id_Time_Stamp := To_Char(To_Date(To_Char(Sysdate,'MMDDYYYY'),'MMDDYYYY'),'YYYYMMDDHH24MISS');
select user into post_by from user_users;
/* select count(*)into cnt_rec from from post_treasury
where post_flag='N'
and To_Date(To_Char(Payin_Date,'MM-DD-YYYY'),'MM-DD-YYYY') <=id_process_date*/
--cnt:= cur_post_payins%rowcount;
For i In Cur_post_payins
Loop
seq_num:= seq_num+1;
payin_line:=payin_line+1;
Insert Into ts_external_payins@tcsh
(dept_payin_no,payin_line_no,payin_Date, cr_dr_flag,
payin_amount, post_flag, gl_fund_code, gl_Org_code,
gl_acct_code,gl_prog_code,payin_comment)
Values
(i.dept_payin_no,payin_line,i.payin_date,i.cr_dr_flag,
abs(i.payin_amount),'N',i.gl_fund_code,
i.gl_Org_code, i.gl_acct_code, i.gl_prog_code,i.payin_comment);
UPDATE POST_TREASURY
SET POST_FLAG='Y', POST_DATE =ID_TIME_STAMP, posted_by =post_by,
payin_line_no=payin_line
WHERE CURRENT OF cur_post_payins;
End Loop;
Update payin_withdrawal
Set Post_Flag ='Y', date_posted =id_time_stamp, posted_by =post_by
where pw_payin_date=payin_dt;
commit;
records_processed:= seq_num;
END;
------------------------------------
This is When-Button-pressed triger which calls the above procedure.
------------
Declare
ln_Records_Processed Number;
lv_Document_Code Varchar2(15);
Alert_Button Number;
Begin
Alert_Button := Show_Alert('ALERT_TREASURY');
If Alert_Button = ALERT_BUTTON1 Then --Yes
Set_Application_Property(Cursor_Style, 'BUSY');
--PROCEDURE IS CALLED HERE WITH USER ENTERED DATE"
post_to_treasury(to_char(ayin_date,'MM-DD-YY'),ln_Records_Processed);
Set_Application_Property(Cursor_Style, 'DEFAULT');
message('Total '||ln_Records_Processed ||' successfully to Treasury System');
message('Total '||ln_Records_Processed ||' successfully to Treasury System');
End If;
Exception
When Others Then
Set_Application_Property(Cursor_Style, 'DEFAULT');
message('Error: Contact Application administrator '|| sqlerrm||'- '||sqlcode );
End;
Let me know if that helps. The same query works fine in SQL
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|