How to convert postgres timestamp to date: yyyy-mm-dd

I need to convert postgres timestamp to date format yyyy-mm-dd in a
sql statement.
pt.created_date below is timestamp format

i.e ... WHERE pt.created_date >= '2008-01-21'

Any help would be greatly appreciated.

Try this:

WHERE pt.created_date >= '2008-01-21'::date

am Tue, dem 11.03.2008, um 10:51:21 -0700 mailte CaseT folgendes:

Hi All,

I'm a novice but learning quickly and I'm stumped on how to do this.

I need to convert postgres timestamp to date format yyyy-mm-dd in a
sql statement.
pt.created_date below is timestamp format

i.e ... WHERE pt.created_date >= '2008-01-21'

You can't compare a date or timestamp to a varchar or text. For your
example, cast the date-string to a real date like:

... WHERE pt.created_date >= '2008-01-21'::date

Consider also functions like to_date(), see:
http://www.postgresql.org/docs/current/static/functions-formatting.html

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

On Mar 11, 2008, at 2:50 PM, A. Kretschmer wrote:

i.e ... WHERE pt.created_date >= '2008-01-21'

You can't compare a date or timestamp to a varchar or text. For your
example, cast the date-string to a real date like:

Since which version of Pg?

Queries like the above have worked for me from 7.3 up thru 8.1, which
is my current production environment.

Vivek Khera <vivek@khera.org> writes:

On Mar 11, 2008, at 2:50 PM, A. Kretschmer wrote:

i.e ... WHERE pt.created_date >= '2008-01-21'

You can't compare a date or timestamp to a varchar or text. For your
example, cast the date-string to a real date like:

Since which version of Pg?
Queries like the above have worked for me from 7.3 up thru 8.1, which
is my current production environment.

The above example is not in fact comparing to a varchar or text value.
It's comparing to an unknown-type literal constant, which will
preferentially be resolved as being the same type as the variable
it's being compared to.

Whether you can do a cross-data-type comparison between two variables of
known data types is a whole 'nother story. PG 8.3 insists on an
explicit cast in some cases where earlier versions silently did a
(possibly surprising) type conversion.

regards, tom lane

A. Kretschmer wrote on 11.03.2008 19:50:

am Tue, dem 11.03.2008, um 10:51:21 -0700 mailte CaseT folgendes:

Hi All,

I'm a novice but learning quickly and I'm stumped on how to do this.

I need to convert postgres timestamp to date format yyyy-mm-dd in a
sql statement.
pt.created_date below is timestamp format

i.e ... WHERE pt.created_date >= '2008-01-21'

You can't compare a date or timestamp to a varchar or text. For your
example, cast the date-string to a real date like:

... WHERE pt.created_date >= '2008-01-21'::date

You can also use the ANSI standard for specifying date literals:

WHERE pt.created_date >= DATE '2008-01-21'

which will work with other (standard compliant) DBMS as well.

Thomas