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

Did you run this query on the psql cli? Why is there a stray double quotation mark at the end? Why are you using UPPER when there is no text in a date field? Why did you choose the tag mysqli when it is about psql? — Paul
– Paul, Commented Sep 15, 2016 at 7:22

user330315 · Accepted Answer · 2016-09-15 07:29:41Z

7

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.

answered Sep 15, 2016 at 7:29

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL SELECT LIKE datetime

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related