NLS_DATE_FORMAT

[Former Member]

Hi there, database experts.

Is it possible to change the date format on SAP BW's sessions to the Oracle Database it is running on.

Something like:

ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDu2019;

I know it is possible to change for the whole database, but my DBA is not so keen doin gthis change, and proposes to do the change sessionvise.

Anybody know how to set this in the SAP BW system ?

Best Regards Ingrid

[Former Member · Accepted solution]

Hello Ingrid,

you could write a logon trigger. Every time user SAPSR3 logs on the trigger performs your desired

ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDDu2019;

I am not sure whether the SAP BW system relies on a specific NLS_DATE_FORMAT, so chances are high you'll get problems in SAP BW.

Regards,

Mark

[Former Member · Accepted solution]

Thanks for your answer Mark.

I hope somebody knows more on SAP BW and if it trelies on a spesific data format !

Do you know the syntax for making such a trigger ? ( in the Oracle database right?)

My original problem is that i have issues in reading date fields from another Oracle database, ( Using DB connect).

Postings on SDN suggests to make a view and split the datetime field to to fields. one with date, and another with time.

using to_char and 'YYYYMMDD' as format.

But it is still not working. I found a posting advising to change the NLS_DATE_FORMAT on the database, but have not been able to test that.

Best Regards

Ingrid

[Former Member · Accepted solution]

Hi Ingrid,

if you have some Sandbox system or the like where a downtime isn't crucial, then simply test it. The syntax should be like this:

CREATE OR REPLACE TRIGGER CHANGE_NLS_DATE_FORMAT
AFTER LOGON ON DATABASE
WHEN ( USER = 'SAPSR3' )
BEGIN
execute immediate 'alter session set nls_date_format ''YYYYMMHH''';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

I created the trigger on a demo system and restarted one work process. So far it works as usually. A logon trigger can be easily dropped:

DROP TRIGGER CHANGE_NLS_DATE_FORMAT;

Regards,

Mark

[Former Member · Accepted solution]

Thanks again Mark.

We will test this, since we are in a developmentface.

Have a nice summer.

Ingrid