0

I have an entry like that in my postgreSql DB :

enter image description here

and used this sql request :

SELECT *
FROM "options"

WHERE 'startDate' >= '2021-05-01 00:00:00.000+00' AND 'startDate' <= '2021-05-31 00:00:00.000+00';

That should return me the entry but it doesn't :(

I tried this request :

SELECT *
FROM "options"
WHERE 'startDate' BETWEEN '2021-05-01 00:00:00.000+00' AND '2021-05-31 00:00:00.000+00';

Returns an empty row.

I also made few tests with those request :

SELECT * FROM "options" WHERE 'startDate' > '2021-05-31';

and this returns the entry (it shouldn't because 'startDate' is 2021-05-04... I also replaced 2021 by 2022/2030, no matter what date in the futur, it's returning the entry...

So guys please help!!! Thanks a lot!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
David Cai
  • 11
  • 1
  • You should not quote you column and table names? try something like ``SELECT * FROM options WHERE startDate >= '2021-05-01 00:00:00.000+00' AND startDate <= '2021-05-31 00:00:00.000+00';`` – row Jun 01 '21 at 14:04
  • I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  Jun 01 '21 at 15:00
  • 1
    `'startDate' >= '2021-05-01 00:00:00.000+00'` compares the **string** `'startDate'` with the string `'2021-05-01 00:00:00.000+00'` which is always true because the character `s` is bigger than the character `2`: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=bc102972e13ca95c6298218a617bf0ae –  Jun 01 '21 at 15:16
  • Do not wrap column names in single quotes. I consider this a typo and am voting to close. – Gordon Linoff Jun 01 '21 at 15:34

2 Answers2

1

Thanks for your help and attention! It's working now, I had just to use double quotes for my startDate field like so :

SELECT * FROM "options" WHERE "startDate" >= '2021-05-01' AND "startDate" <= '2021-05-31'

David Cai
  • 11
  • 1
0

You are comparing string to a string. Try:

SELECT * FROM options WHERE startDate > '2021-05-31';

If above does not work, try using double quotes around column names.

SELECT * FROM options WHERE "startDate" > '2021-05-31';

Example: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=cdeaaf19845d51ea227ac18ed454857a

More info "why": https://stackoverflow.com/a/2901499/1727039

Mr. P
  • 1,387
  • 1
  • 11
  • 25
  • Hey Mr.P thanks for your reply! In fact, this was my first test : `const lastMonthFirstDate = moment().subtract(1,'months').startOf('month').format('YYYY/MM/DD'); SELECT * FROM "options" WHERE 'startDate' >= '${lastMonthFirstDate}'` that returns me the entry, but it returns the entry even if I use this request with a date from futur : SELECT * FROM "options" WHERE 'startDate' > '2023-05-31'; That returns me the entry too :/ – David Cai Jun 01 '21 at 14:11
  • In the comment you are still comparing strings. Omit the quotes. Take a look at https://dbfiddle.uk/?rdbms=postgres_13&fiddle=239df2b4470e66ccb4af63bbab912e57 – Mr. P Jun 02 '21 at 11:25