TO_DATE function between PostgreSQL 8.2 and 8.3

As we all know PostgreSQL 8.3 make very strong checking on data type, it make a lot application hit many data type error after migration from PostgreSQL8.x to PostgreSQL8.3. Mostly is cause by data type checking.

One of the common error is to_date() function. It accept two text as parameters.


to_date(text,text)

Ok now i create a table as following. A simple table call n_url_test contain a createddate as timestamp data type.


CREATE TABLE n_url_test
(
  urltestid bigserial NOT NULL,
  createddate timestamp without time zone DEFAULT now(),
  CONSTRAINT n_url_to_test_pkey PRIMARY KEY (urltestid)
)

I run following sql in PostgreSQL8.2, it return my expected result as ‘YYYY-MM-DD’ format.


--PostgreSQL 8.2
---------------
select  to_date(createddate,'YYYY-MM-DD') from n_url_test;

However after i migrated to PostgreSQL8.3, i hit following error


--PostgreSQL 8.3
---------------
select  to_date(createddate,'YYYY-MM-DD') from n_url_test;

--------------------------------------------------------------------------------
ERROR:  function to_date(timestamp without time zone, unknown) does not exist
LINE 1: select  to_date(createddate,'YYYY-MM-DD') from n_url_test
                ^
HINT:  No function matches the given name and argument types. 
You might need to add explicit type casts.

********** Error **********

PostgreSQL 8.3 will not automatically convert from timestamp to text for us like what it does in previous version. We need to explicit type casts createddate like following


--PostgreSQL 8.3
---------------
select  to_date(createddate::text,'YYYY-MM-DD') from n_url_test;

Append ::text to createddate will explicit convert datatype as text, It’s work. PostgreSQL8.3 strictly checking on data type is good function , but please do not forget there still have a lot people using previous version. It cause a lot problem if application is design base on ignore data type checking like what i mention above.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
5 Comment threads
3 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
6 Comment authors
Kiran NaiduCarmeloTO_DATE function between PostgreSQL 8.2 and 8.3 « DbRunas – Noticias y Recursos sobre Bases de Datosjlamkyong Recent comment authors
newest oldest most voted
Kiran Naidu
Guest
Kiran Naidu

how to use between with two database columns i.e(restrictions.between(fromDate,”startDate”,”endDate”);

jla
Guest
jla

It looks like you are using to_date where to_char would be more appropriate: to_date(text,text) – convert string to date to_char(timestamp, text) – convert time stamp to string Instead of relying on the old implicit cast of timestamp to text ISO 8601 format, having to_date parse it according to you ‘YYYY-MM-DD’ format, convert it into a date object and then spit it out as ISO 8601 text (effectively truncating all but the date part of the timestamp), you could use to_char: SELECT to_char(createddate,’YYYY-MM-DD’) FROM n_url_test; Since to_char expects a timestamp there should not be a casting error and you aren’t turning… Read more »

Windows tips
Guest
Windows tips

Does this tutorial still work on the latest PostgreSQL version???

software development company
Guest
software development company

Interesting,

I have question if you know other programming languages is it faster yo use a store procedure or just getting the data a nd filtering it with a programming language

Anyway, thanks for the post

trackback
TO_DATE function between PostgreSQL 8.2 and 8.3 « DbRunas – Noticias y Recursos sobre Bases de Datos

[…] http://www.mkyong.com/database/to_date-function-between-postgresql-82-and-83/ June 27, 2011   //   PostgreSQL   //   No Comments   //   […]