CODEWITHSUNDEEP
oracle-database
Jajan
Jajan

Reputation: 926

Same Function returning different results in Oracle

I run two queries and they are returning different results. Please help me find what am I missing.

select UPPER(TO_CHAR(Hire_date,'MONTH'))
from employees

returns a list of months(containing "2" entries for 'MARCH')

SELECT COUNT(EMPLOYEEID) "March Joinees"
FROM Employees
WHERE UPPER(TO_CHAR(Hire_date,'MONTH')) = 'MARCH';

returns 0 as count

Upvotes: 0

Views: 54

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

As stated by mathguy already, 'MONTH' makes padding with spaces. Try 

SELECT '"'||TO_CHAR(Hire_date, 'Month')||'"' FROM employees

to see the effect. Use either function TRIM or Format Model Modifiers FM

Then result of TO_CHAR(Hire_date, 'Month') depends your current session NLS_DATE_LANGUAGE value. Other session may get "März" or "Μάρτιος". Specify either date language or use Month numbers.

Actually UPPER(TO_CHAR(Hire_date,'MONTH')) is redundant. Format MONTH returns month name in upper case, you don't have to make UPPER() again.

Taking all this into account you should use one of the expressions below

WHERE TO_CHAR(Hire_date, 'fmMONTH', 'NLS_DATE_LANGUAGE = english') = 'MARCH'

WHERE UPPER(TO_CHAR(Hire_date, 'fmMonth', 'NLS_DATE_LANGUAGE = english')) = 'MARCH'

WHERE TRIM(TO_CHAR(Hire_date, 'MONTH', 'NLS_DATE_LANGUAGE = english')) = 'MARCH'

WHERE TO_CHAR(Hire_date, 'MM') = '03'

WHERE EXTRACT(MONTH FROM Hire_date) = 3

Upvotes: 1

user5683823
user5683823

Reputation:

to_char(<date>, 'Month') produces a string of length equal to the greatest length of any month name (in the current session's language) - it does so by padding with spaces. This is why the second query produces no results; if you wrap the upper.... within trim(...) it will work, but it would be better not to use to_char(..., 'Month') for this kind of query to begin with. I am sure you came to the same conclusion but you just want to know what is going on...

Here's an illustration. It may seem like the first column in the result is the string 'March'; however, the second column doesn't lie: the result in the first column is actually 'March ' (with four spaces at the end).

select to_char(date '2016-03-01', 'Month')           as month_as_string,
       length( to_char(date '2016-03-01', 'Month') ) as len
from   dual
;

MONTH_AS_STRING  LEN
---------------  ---
March              9

Then you may ask why Oracle made such a weird choice. That's a much tougher question. Anyway, this behavior is documented. http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924 and scroll down to MONTH in table 2.15.

Upvotes: 2

Related Questions