PostgreSQL Data Type Formatting Function

Question

I have the following query. I need an output which would look like this

ea_month    case    ea_year    zone_id
January     0       2013       4001
February    0       2013       4002
March       1       2013       4001
January     0       2014       4001
February    0       2014       4001
March       1       2014       4001
February    0       2014       4002
March       1       2014       4002

SELECT ea_month,CASE WHEN ea_month = (SELECT to_char(now(), 'Month')) THEN 1 ELSE 0 END,ea_year,zone_id FROM staging_ea.stg_circle_zone_billedamount_rollup

In my table I have my values for ea_month in the form of January, February, March.

After running the above query, the result-set that I am getting is something like this:

ea_month    case    ea_year    zone_id
January     0       2013       4001
February    0       2013       4002
March       0       2013       4001
January     0       2014       4001
February    0       2014       4001
March       0       2014       4001
February    0       2014       4002
March       0       2014       4002

Can anyone suggest me where am I going wrong? Its for a PostgreSQL query.

You don't need select in your CASE statement. case when ea_month = to_char(now(), 'Month') then ... should do it. Of course that is subject to environment settings: the name of the month returned by the to_char() function depends on the locale settings of the current user. — user330315
– user330315, Commented Mar 27, 2014 at 9:44
I have tried it without the SELECT as well but to no effect. — Yousuf Sultan
– Yousuf Sultan, Commented Mar 27, 2014 at 9:45
Did you make sure to_char() actually returns 'January' in English and not in some other language? What does SELECT to_char(now(), 'Month') return on its own? — user330315
– user330315, Commented Mar 27, 2014 at 9:55

user330315 · Accepted Answer · 2014-03-27 10:05:36Z

1

This is caused by the to_char() function padding the month name with spaces. This can either be fixed by applying a trim() on the result or using the FM modifier in the format mask:

SELECT ea_month,
       CASE 
          WHEN ea_month = to_char(current_date, 'FMMonth') THEN 1 
          ELSE 0 
       END,
       ea_year,
       zone_id 
FROM staging_ea.stg_circle_zone_billedamount_rollup;

See the manual for details: http://www.postgresql.org/docs/current/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE

answered Mar 27, 2014 at 10:05

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Data Type Formatting Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related