Compare a date and a timestamp with time zone with now() in the same query?
This should never fail (I simplified a bit):

WHERE status_code IN ('30000','30005')
AND   expiration &gt; now() 


PostgreSQL can compare date and timestamp (with or without time zone) automatically. If one is a date it is cast to timestamp automatically (0:0 hours).

The error message tells a different story. You are actually trying to input a date with invalid syntax.

I wrote a detailed answer about handling of timestamps with or without time zone in PostgreSQL recently - if that should the issue:


Ignoring timezones altogether in Rails and PostgreSQL




As it turns out, expiration is a text column. You need to cast it to date or timestamp (whichever fits your need). If it is in a valid format:

...
AND   expiration::timestamptz &gt; now() 


If you have invalid strings like 'No End Date' in that text column, you need to clean the source or treat those specially in a CASE construct:

...
AND   CASE WHEN expiration::text = 'No End Date' THEN 'infinity'::timestamp
           WHEN expiration::text = 'foo' THEN '-infinity'::timestamp
           ELSE expiration::timestamp
      END &gt; now()


The manual about the special value infinity.

The cast to text (::text) is redundant with text, but makes the expression work with date / timestamp values as well.

If the format of the time stamp literal can be ambiguous, use to_date() or to_timestamp() and define the format explicitly:

to_date('07/08/2013', 'DD/MM/YYYY')



How to convert “string” to “timestamp without time zone”


Use the AT TIME ZONE construct if expiration is supposed to be a local timestamp of another time zone:

expiration::timestamp AT TIME ZONE 'UTC' -- desired time zone here


If expiration is in ambiguous / non-standard format, use to_timestamp():

to_timestamp(expiration::text, 'yyyy-mm-dd')