-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|