DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: to_char, to_date

  1. #1
    Join Date
    Sep 2002
    Posts
    20
    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:-(

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  3. #3
    Join Date
    Sep 2002
    Posts
    20
    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
  •  


Click Here to Expand Forum to Full Width