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