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 ?

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')

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