"Campano, Troy" <Troy.Campano@LibertyMutual.com> writes:
> anna> SELECT request_date,TO_DATE(request_date,'YYYY-MM-DD HH24:MM:SS')
The correct way to write the format string would have been
SELECT request_date,TO_DATE(request_date,'YYYY-MM-DD HH24:MI:SS')
(minutes are MI not MM). It was evidently taking the minute number as
month number, and not noticing that the field was out of range :-(.
People have complained before that to_date() and related functions don't
detect all the error cases one would reasonably expect them to complain
about ...
However, this all seems like the hard way to solve your problem.
Why don't you just cast the timestamp value to date type, ie
"CAST(request_date AS date)", or just "request_date::date" if you don't
mind using a Postgres-specific syntax. The date_trunc() function also
is worth knowing about.
regards, tom lane