I have been trying to solve the problem that I encounter when I call a Oracle function in my code. Oracle function takes one number and date as parameteres:
MY_FUNCTION (NO IN NUMBER,DATE IN DATE)
And I was calling the function in my code like
This code is already a wrong approach; trying to convert sysdate which is already a date to a date. I couldn’t change it as it is used by many other applications. But the same code above gave different results in different servers. I realized that the problem was the date format. That is to say how server interprets sysdate parameter of oracle.
When I called to_date(sysdate) (select to_date(sysdate) from dual;) I got 09.12.2015 normally but in a new server I got 09.12.0015 which caused problem for my oracle function.
I thought that as it is an Oracle term, sysdate gave the current datetime from Oracle server but it was not.
Depending on the client server’s settings, the date format might differ. There are two options you can do:
1. By changing the datetime format of server from registry.
Regedit > HKEY_USERS > .DEFAULT -> Control Panel -> International
Find sShortDate and right click > Modify
Now modify the format now.
2. If this doesn’t work, in my case It did not, I reinstalled Oracle Client with Administrator option. This installs all needed components of the client. Then you can modify NLS_DATE_FORMAT attribute which overrides default value of Oracle Server’s in the registry:
Thanks to A. Mülver.