PostgreSQL SELECT LIKE datetime

Question

I have an input to search for what date to show, the input can be a date format (yyyy or yyyy-mm or yyyy-mm-dd) like '2006' or '2017-01' or '2008-10-10', i use query like this

SELECT * FROM MEMBER WHERE UPPER(join_date) LIKE UPPER(%".$input."%);

but the result is empty, is there a way to correctly use like statement for datetime in postgresql

Answer 1

LIKE is for strings, not for DATE values. That means you must first convert the date to a properly formatted string value:

SELECT * 
FROM member
WHERE to_char(join_date, 'YYYY-MM-DD') LIKE '%.... %';

Using upper(join_date) is subject to the evil implicit data type conversion and will not work reliably.

Also: upper() on a string with only numbers doesn't really make sense.