Dynamic SQL and Date format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Dynamic SQL and Date format

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    Slovakia, Europe
    Posts
    72

    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;

    BEGIN
    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;
    LOOP
    ...

    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
    Location
    Slovakia, Europe
    Posts
    72

    i've got it

    well,

    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