Home » SQL & PL/SQL » SQL & PL/SQL » TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY')  () 1 Vote
TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY') [message #216013] Wed, 24 January 2007 11:33 Go to next message
IT Guru
Messages: 59
Registered: January 2007
Member
Given following 2 querys on sample table employees.

Both return diffrent row. But can't identify on what gorud it is working. as both the column look exactly same in list.
Quote:
TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY')


Quote:
(1st Query)

SELECT employee_id, hire_date,TO_CHAR(hire_date,'fmDD MONTH YYYY') Month_Hireed,TO_CHAR(hire_date,'DD MONTH YYYY')
FROM employees
WHERE TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY');

Quote:
(2ndt Query)

SELECT employee_id, hire_date,TO_CHAR(hire_date,'fmDD MONTH YYYY') Month_Hireed,TO_CHAR(hire_date,'DD MONTH YYYY')
FROM employees
WHERE TO_CHAR(hire_date,'fmDD MONTH YYYY') = TO_CHAR(hire_date,'DD MONTH YYYY');

[Updated on: Wed, 24 January 2007 11:35]

Report message to a moderator

Re: TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY') [message #216017 is a reply to message #216013] Wed, 24 January 2007 11:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The fm format model, that people commonly refer to as fill mode, removes the extra spaces. The result of to_char is a varchar2 character data string. You are comparing one such character string without extra spaces to one with extra spaces. So there will be some that are the same, because they did not have extra spaces, and some that are not the same, because the extra spaces were removed from one. Extra spaces can result from things like a one-character day that is displayed with an extra leading space to allow for two-character days or a month that is shorter than the longest month, that allows for extra spaces for longer months.
Re: TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY') [message #216124 is a reply to message #216017] Thu, 25 January 2007 02:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's an example of the effect of the FM format modifier:
SQL> select to_char(sysdate+lvl,'dd month yyyy'),to_char(sysdate+lvl,'fmdd month yyyy')
  2  from (select level*10 lvl from dual connect by level <= 10);

TO_CHAR(SYSDATE+L TO_CHAR(SYSDATE+L
----------------- -----------------
04 february  2007 4 february 2007
14 february  2007 14 february 2007
24 february  2007 24 february 2007
06 march     2007 6 march 2007
16 march     2007 16 march 2007
26 march     2007 26 march 2007
05 april     2007 5 april 2007
15 april     2007 15 april 2007
25 april     2007 25 april 2007
05 may       2007 5 may 2007
Re: TO_CHAR(hire_date,'fmDD MONTH YYYY') <> TO_CHAR(hire_date,'DD MONTH YYYY') [message #216224 is a reply to message #216013] Thu, 25 January 2007 12:20 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
STOP COMPARING TEXT STRINGS TO TEXT STRINGS!!!

Your query...

SELECT employee_id, hire_date,TO_CHAR(hire_date,'fmDD MONTH YYYY') Month_Hireed,TO_CHAR(hire_date,'DD MONTH YYYY')
FROM employees
WHERE TO_CHAR(hire_date,'fmDD MONTH YYYY') = TO_CHAR(hire_date,'DD MONTH YYYY'); 


compares a string to a string. For example, if the hire date was
the 6th of January in 2005:

TO_CHAR(hire_date,'fmDD MONTH YYYY') = 6 January 2005
TO_CHAR(hire_date,'DD MONTH YYYY') = 06 January 2005
TO_CHAR(hire_date,'MM/DD/YYYY HH24:MI:SS') = 01/06/2005 13:00:00

All three have the same hire_date value. Only the printed format is different. Your WHERE clause above would succeed for dates from the 10th of the month on, since the "fm" portion suppresses the leading 0 of single digit dates.

Comparisons of dates in a WHERE clause ought to be done with the raw dates, not the string value returned from the TO_CHAR function.

Hope this helps,
Ron
Previous Topic: Merge problem (merged)
Next Topic: calling pipeline table function in Pro*C
Goto Forum:
  


Current Time: Fri Dec 02 12:36:33 CST 2016

Total time taken to generate the page: 0.24353 seconds