How to get current NLS_DATE_FORMAT?

[marcarls]

Hi,

Is there anyway to get hold of the currect NLS_DATE_FORMAT setting on database level from the database? I need to check which settings our diffrent clients around the world have and then write a function that depending on this settings converts the date with correct format mask.

Any suggestions?

Regards Maria

 

[appi]

Hi Maria,

one way will be to use the command
show parameter NLS_DATE_FORMAT
from sqlplus.

Hope that helps
Uwe

 

[contactop]

Hi,
You can wirte a procedure with a if condition checking the country and then accordingly you set the NLS_DATE_FORMAT

create procedure ppp (country as VARCHAR)
begin
if country = 'XXX' then
SET NLS_DATE_FORMAT = 'mm/dd/yyyy';
elsif ....
SET NLS_DATE_FORMAT = 'dd/mm/yyyy';
end if;

end;
like this you can do it.

 

[sem]

I don't suggest to follow contactop's advice, mostly because he uses some unknown language (apparently not PL/SQL). Uwe's example will work in sql*plus and some other tools, that imitate its command set. You may use pure sql:

select value from v$parameter where name ='nls_date_format'

Of course you should be granted select privilege on this view


Regards, Dima