DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003
    Slovakia, Europe

    Lightbulb Dynamic SQL and Date format

    Hi everybody !

    I need to use dynamic sql for a query. In query there should be sometimes a DATE condition but sometimes not. The date value is comming as the input procedure parameter. I dont want to use "USING" keyword. And the problem is... how could I get a correct date format, when I the input date parameter is in known format ?

    Can I get the session date format and use it in date formating in pl/sql ?

    PROCEDURE tryit (
    p_statement_date CHAR(10); // allways in 'dd/mm/yyyy'
    ) IS
    lv_sqlStat VARCHAR2(1000);
    lv_statement_date DATE;

    lv_statement_date := TO_DATE(p_statement_date,'dd/mm/yyyy');

    sqlStat := 'SELECT ... FROM ...';

    IF p_statement_date IS NOT NULL THEN
    sqlStat := sqlStat || 'WHERE activeDate = ' || TO_CHAR(lv_statement_date,'dd.mm.yyyy')
    END IF;

    OPEN dataCursor FOR sqlStat;

    END tryit;

    Can I get somehow session date format ? If yes, will it work for all tables in db ? and how ?
    When I change session date format, will it work for all tables, regardless the instance/database format date is the other one ?

    Thank you

  2. #2
    Join Date
    Feb 2003
    Slovakia, Europe

    i've got it


    if you convert date variable param to char with function TO_CHAR (to_char(date)) it causes that the output string will be in session.nls_date_format format.

    so don't use TO_CHAR(date,'dd.mm.yyyy') but TO_CHAR(date) and everything is ok

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.