phpmyadmin Question posted in
The official documentation can be found here.

sql query "where clause" with two date columns – Phpmyadmin

I have one date column and another date column for the invoice date.
Example :
enter image description here

While in Excel I can just filter the InvDate column into "before 01-12-20" and filter the DATE column into "Last Month" then it give me the expected result like below :
enter image description here

So, I’ve tried the SQL like this:

SELECT DATE, InvDate FROM `MyTable` 
WHERE InvDate < '2020-12-01'
AND DATE >= '2020-12-01'
AND DATE <= '2020-12-31'

so I thought the first "where" means : all the invoices date before 1 Dec 2020,
the 2nd and the 3rd "where" means : all the payment date within Dec 2020 (between 1 Dec 2020 to 31 Dec 2020) so then I thought the result will be all the invoices date (dated on any month but before Dec 2020) which paid on Dec 2020, but the result look like this :
enter image description here

Both InvDate and DATE of the table columns are in date format.
I wonder how do I do the SQL.

Any kind of response would be greatly appreciated.
Thank you in advanced.

This question should include more details and clarify the problem.

The problem is that I can’t get the expected result with that SQL I wrote.
The expected result is to get the invoice date before Dec 2020 which paid in Dec 2020. In other words : "Here are the invoices dated before Dec 20 which paid on Dec 20"

About the "more details", I’m sorry as I don’t know how to describe the beginning of my situation to be more details. Should I give the database host, username and password ?

Tags:

2

Answers


  1. 0

    Your results suggest that you have rows with essentially a NULL value of the invoicedate. If the column is given a default value of 0, then you will see the date you do — or if the value is explicitly inserted. Here is an example.

    More recent versions of MySQL do not allow '0000-00-00' as a date value, unless you fiddle with the default settings.

    You can filter these out by going back far in time and adding that to the where:

    SELECT DATE, InvDate FROM `MyTable` 
WHERE InvDate < '2020-12-01' AND
      InvDate >= '1900-01-01' AND
      DATE >= '2020-12-01' AND
      DATE < '2021-01-01';

    Notice that I switched the inequality on the date comparison. This makes it easier to switch to different months — you don’t have to remember the last date of the month.

    Login or Signup to reply.
  2. 0

    It looks like 0000-00-00 is being used in invdate to mean invoice date unknown or n/a.

    You could add :
    AND invdate <> ‘0000-00-00’

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

Back To Top
Search