Question 89023 in PostgreSQL Question posted in
The official documentation can be found here.

Getting error for filtering current_date in Postgresql

I have 2 queries in Postgresql. Both Queries are used to extract same information.

1st Query—-

SELECT "pld.PlanDate" 
FROM salesdespatch.sld_cube_salesdespatchdetailshop
WHERE "pld.PlanDate" Between '2023-04-01' AND current_date;

2nd Query—-

SELECT "pld.PlanDate" 
FROM salesdespatch.sld_cube_salesdespatchdetailshop
WHERE "pld.PlanDate" Between '2023-04-01' AND '2023-04-11';

When I am running the 1st query, pgAdmin provides an error and the error is explained below.

ERROR:  operator does not exist: text <= date
LINE 2: WHERE "pld.PlanDate" Between '2023-04-01' AND current_date;
                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 96

However, the 2nd query is running without any error and I have not changed any syntax in the second query. I have also run Current_date syntax explicitly and it is working fine as well.
I am unable to find what is the source of the error.
Can anyone please explain?

I am also attaching 2 screen shots for the results of 1st and 2nd query for better explanation.

Result of 1st Query

Result of 2nd Query

I am trying to filter data between a selected date and current date but got an error that I have explained above.

Tags:

2

Answers


  1. 0

    It appears that the PlanDate column is text, and not a date type. Don’t store your dates as text. As a workaround, given that your text dates are in an ISO format, you may cast the column to date in the query:

    SELECT "pld.PlanDate"
FROM salesdespatch.sld_cube_salesdespatchdetailshop
WHERE "pld.PlanDate"::date BETWEEN '2023-04-01' AND current_date;
    Login or Signup to reply.
  2. 0

    it seems like either date is in text or you can use date(now()) instead of current_date.
    date(now()) will work same as current_date.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search