Main Tutorials

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 Author

author image
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

Subscribe
Notify of
7 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Kiran Naidu
9 years ago

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

jla
13 years ago

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 it into a date object. It goes directly to text using the formatting string of your choosing.

Windows tips
13 years ago

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

Carmelo
11 years ago
Reply to  mkyong

Can you tell me how to fix this error.

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: operator does not exist: date ~~ unknown LINE 1: …tment_table.id_pat=638 and treatment_table.datein like ‘2012… ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in C:\wamp\www\gmc\datemenu.php on line 695

Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\gmc\datemenu.php on line 697

Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\gmc\datemenu.php on line 699

Carmelo
11 years ago
Reply to  Carmelo

695 $ress= pg_exec($conn,$query);
696
697 $max=pg_num_rows($ress);
698
699 for($j=0; $j<pg_num_rows($ress); $j++){

software development company
14 years ago

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