0

I'm calling an Oracle procedure with this code:

begin
PPT2000_AGG_HOURLY.RUNDURATION_HOURLY(4, to_date('01112014', 'DDMMYYYY'), 20);
end;
/

My problem is the date format. On one PC the procedure calculates all fine with the result for the date 01.11.2014 00:00.

On the other PC (same version of Oracle SQL Developer) the procedure returns 0 for the calculation and for the date 01.11.2014

So something seems to be different in converting the date and I don't know why. I tried it with a third PC and get also the return of 01.11.2014.

The PC which gives the correct result has the same operating system (WIN7 German) and the same version of the Oracle SQL Developer. Also the same Oracle database.

So how could this be possible?

Update Solution: Thanks for the answers, I changed the NLS-Settings from DD.MM.RR to DD.MM.RR HH24:MI and now it works :)

Kᴀτᴢ
  • 2,146
  • 6
  • 29
  • 57
  • 1
    How could what be possible - that the date is displayed differently somewhere? Check the NLS_DATE_FORMAT for the clients. If that is appearing in some output from your procedure, e.g. if it's writing to a file, then your procedure code is relying on that session setting, which is a bad idea. – Alex Poole Apr 16 '15 at 12:13
  • You should handle the display format in the code, and not depend on the client's NLS_DATE_FORMAT. You can't expect 1000 users to change their local NLS settings in their GUI based client tools. – Lalit Kumar B Apr 16 '15 at 13:03

3 Answers3

3

You should handle the display format in the code, and not depend on the client's NLS_DATE_FORMAT. You can't expect 1000 users to change their local NLS settings in their GUI based client tools.

A date doesn't have a format. What you see, is just for display depending on the locale-specific NLS_DATE_FORMAT of the client.

And the NLS_DATE_FORMAT could be overridden at different levels. If you don't want to depend on the client's NLS settings, then always use TO_CHAR with desired format mask to make sure it is overridden at individual statement level.

Remember, for diaplaying DATE values, always use TO_CHAR with desired format. To do date arithmetic, use TO_DATE to convert a literal into DATE.

For example,

TO_CHAR(date_column, '<desired_format>')

It will override the locale-specific NLS_DATE_FORMAT at statement level.

See this similar answer for more understanding.

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

If you really have a constant date then use the Oracle DateTime literal. It works every time and it's independent of the NLS_DATE_FORMAT:

begin
  PPT2000_AGG_HOURLY.RUNDURATION_HOURLY(4, DATE '2014-11-01', 20);
end;
/
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
0

Check the NLS preferences in SQL Developer to see if there is a mismatch. Go to Preferences->Database->NLS. You can also override these settings on a per session basis if you want.

Necreaux
  • 9,451
  • 7
  • 26
  • 43
  • Note that what Lalit says is important to consider. Knowing that the NLS settings are what is causing this is a good first step. Fixing it may need to be considered carefully if this is unlikely a "one-off" issue. – Necreaux Apr 16 '15 at 13:01