Oracle sysdate year problem

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

MY_FUNCTION(15123,to_date(sysdate,’dd.mm.yyyy’))

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.

sysdate_iis_oracle

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:

Oracle_nls_date_format

Thanks to A. Mülver.

Advertisements
This entry was posted in ORACLE and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s