ALTER SESSION might match your needs for now, but it is a very bad habit IMO to rely on the session date format. I'm pretty sure you will regret it in the long term. So as a short-term solution that can be OK, but keep in mind that this should be changed in the future.
What we do in order to use the same format everywhere we need to is using a date format as a package variable and use it instead of hard-coded string format. For example :
This does not work in pure SQL since package variables cannot be called from there, but works perfectly in all PL/SQL code. For example :Code:CREATE OR REPLACE PACKAGE PKG_Dates AS GstrDateFormat VARCHAR2(25) := 'YYYY/MM/DD HH24:MI:SS'; GstrTimestampFormat VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS.FF6'; END PKG_Dates; /
HTH & Regards,Code:rbaraer@Ora10g> var rc REFCURSOR rbaraer@Ora10g> BEGIN OPEN :rc FOR SELECT TO_CHAR(SYSDATE, PKG_Dates.GstrDateFormat), TO_CHAR(SYSTIMESTAMP, PKG_Dates.GstrTimestampFormat) FROM DUAL; END; 2 3 4 5 6 7 / PL/SQL procedure successfully completed. rbaraer@Ora10g> PRINT rc TO_CHAR(SYSDATE,:B2) --------------------------------------------------------------------------- TO_CHAR(SYSTIMESTAMP,:B1) --------------------------------------------------------------------------- 2006/08/22 10:49:54 2006/08/22 10:49:54.949515 rbaraer@Ora10g>
rbaraer




Reply With Quote