CODEWITHSUNDEEP
databasepostgresqldatetime
Idham Choudry
Idham Choudry

Reputation: 629

PostgreSQL SELECT LIKE datetime

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

Upvotes: 4

Views: 9754

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 7

Related Questions