Thread: Why this query does not work?

This works:

service0=# SELECT DISTINCT TO_CHAR(session_start, 'Month') as start,
service0-#                             TO_CHAR(session_start, 'Month') as 
end
service0-# FROM                   user_sessions
service0-# WHERE                 TO_CHAR(session_start, 'YYYY') = '2003';    start   |    end
-----------+----------- April     | April
(1 row)

Why this does not gives correct result?

service0=#
service0=# SELECT DISTINCT machine_id
service0-# FROM                   user_sessions
service0-# WHERE                 TO_CHAR(session_start, 'Month') = 'April';

machine_id
------------
(0 rows)

When this does have no problem?

service0=# SELECT DISTINCT machine_id
service0-# FROM                   user_sessions
service0-# WHERE                 TO_CHAR(session_start, 'Month') = (SELECT 
DISTINCT    TO_CHAR(session_start, 'Month')
service0(#                                                    FROM   
user_sessions);

machine_id
------------       123
(1 row)

Thank you for your time.

DanielD

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

"Katka a Daniel Dunajsky" <daniel_katka@hotmail.com> writes:
> Why this does not gives correct result?

You've missed the trailing blanks in TO_CHAR's result.

Probably you should be using FMMonth not Month as the format specifier.
        regards, tom lane

> service0=#
> service0=# SELECT DISTINCT machine_id
> service0-# FROM                   user_sessions
> service0-# WHERE                 TO_CHAR(session_start, 'Month') = 'April';
> 
> machine_id
> ------------
> (0 rows)
> 

Since to_char function pads space to the value returned. The length of the 
return value of to_char function is 9. It is mentioned in the document, 
please refer the below link for details:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html

You can use rtrim to get rid of your problem. 

=> select distinct machine_id from user_sessions where rtrim (to_char
(session_start, 'Month')) = 'April';

regards,
bhuvaneswaran