effect of nls_territory

[amoroder]

Hello,

in the last weeks we have a strange problem with a simple query with this where statement
where nvl(:p_Data, trunc(sysdate)) between co_naz_inizio and
nvl(co_naz_fine, nvl(:p_Data, trunc(sysdate)))

We get the message ORA 01801 date format too long when we pass no value to :p_data.
I tried to solved it by adding to_date

select * from codif_nazioni
where nvl(to_date(:p_Data,'DD.MM.YYYY'), trunc(sysdate)) between co_naz_inizio and
nvl(co_naz_fine, nvl(to_date(:p_Data,'DD.MM.YYYY'), trunc(sysdate)))

and it works as long as NLS_TERRITORY is not set.
We have one location where this is set and on this DB the query returns no records.
This location does not get the ORA 01801

Can you tell me why the NLS_TERRITORY changes the behaviour of this simple query?

Thanks
Andreas

[MaximG]

Please specify what type in your project has the :p_Data ? parameter. In case if DataType=ftDate then some additional conversion (to_date(:p_Data,'DD.MM.YYYY') is not required.