postgresql - Netezza TO_CHAR Function not evaluating appropriately? -

i having issues query that, if ran hard-coded dates, insert correct number of rows table (170k+). issue is, when try automate it, replacing hard-coded dates date functions, query insert single row newly truncated table.

example hard-coded date: '20150401'

sample of same date, using date function:

to_char(last_day(add_months(now(), -3))+1, 'yyyymmdd')  

the above to_char function returns desired result, when ran separately.

here cleaned-up version of query results in single row being inserted:

insert schema.insert_table(  select to_char(now(), 'yyyymm') tran_month,      sum(case when b.date = to_char(last_day(add_months(now(), -3))+1, 'yyyymmdd')         'do stuff'         end) stuff1,     sum(case when b.date = to_char(last_day(add_months(now(), -3))+1, 'yyyymmdd')         'do other stuff'         end) stuff2,     sum(case when b.date = to_char(last_day(add_months(now(), -3))+1, 'yyyymmdd')         'do weird stuff'         end) stuff3,     sum(case when b.date = to_char(last_day(add_months(now(), -3))+1, 'yyyymmdd')         'do really weird stuff'         end) stuff4,     sum(case when a.code= 1         'do ... '         end) stuff5,   (select col1, col2...  table_a) a, (select col1, col2...  table_b) b, (select col1, col2...  table_c) c, (select col1, col2...  table_d) d, (select col1, col2...  table_e) e,  'conditions a, b, c, d, , e met'     , b.date = to_char(last_day(add_months(now(), -3))+1,'yyyymmdd') group of things order ); 

i have done quite bit of testing, , research, haven't found possible cause why amount of records returned drastically different.

thank you,

justin

i think it's because added 1 character string resulting last_day function. check parentheses:

where 'conditions a, b, c, d, , e met'     , b.date = to_char(last_day(add_months(now(), -3)+1) 

if isn't (or want add 1 character string), i'm going go out on limb , assume b.date column of type date. if so, reason isn't comparing correctly because you're relying on implicit conversion. change date filter explicitly convert both sides.

where 'conditions a, b, c, d, , e met'     , b.date::date = (last_day(add_months(now(), -3)+1)::date