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

How to select the information from a specific time? I'm using phpmyadmin

enter image description here

The datatype of DATEInvoice is VARCHAR

From the image above, if I want to select the information of the invoice from specific time let say from 1-12-2020 00:00:00 to 13-12-2020 23:59:00, how can I write? I have try many ways to write. For example,

SELECT * FROM INVOICE WHERE DATEINVOICE BETWEEN To_date('01-12-2020 00:00:00','dd-mm-yyyy HH24:MI:SS') AND To_date('13-12-2020 23:59:00','dd-mm-yyyy HH24:MI:SS')

**BUT THE RESULT I GET IS RETURN ZERO ROWS. Hope anyone can help me, I am having trouble on this.

Tags:

2

Answers


  1. 0

    First, you can simplify your logic to:

    WHERE DATEINVOICE >= '2020-01-12' AND
      DATEINVOICE < '2020-01-14'

    In some databases, you should identify date constants with the (standard) DATE keyword:

    WHERE DATEINVOICE >= DATE '2020-01-12' AND
      DATEINVOICE < DATE '2020-01-14'

    This assumes that DATEINVOICE is stored as a valid datetime/timestamp value in your database. If it is a string, then it has the wrong type. You should fix your data model. If someone else has created such a broken data model, then you can convert the value in the WHERE clause:

    WHERE TO_DATE(DATEINVOICE, 'dd-mm-yyyy HH24:MI:SS') >= '2020-01-12' AND
      TO_DATE(DATEINVOICE, 'dd-mm-yyyy HH24:MI:SS') < '2020-01-14'

    However, the effort should really be on fixing the data model.

    Login or Signup to reply.
  2. 0

    Can use Please try MySQL’s DATE() function:

    WHERE DATE(datetime) = '2009-10-20'

    You could also try this:

    WHERE datetime LIKE '2009-10-20%'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search