0

In my sql query i have to select particular date from start date 00:00:00 to end date 23:59:59. But how could I input the hours and minutes if the date values are not hardcode. They are input in a form in frontend then it run the particular report. Below is my code any ideas?? It should be like this

BETWEEN '2019-06-00 00:00:00' AND '2019-06-10 23:59:59'

       $start_date = $_GET['start_date'];
       $end_date  = $_GET['end_date'];

       $sql = "SELECT 
       outlets.outletname,
       outlets.barcode,
       invoiceissued.invoiceno, 
        invoiceissued.total_amount, 
       invoiceissued.VATAmount, 
       invoiceissued.date_created,
       outlets.businessregistrationno,
       outlets.vat

       FROM `invoiceissued` , outlets

       WHERE
       invoiceissued.outletid = outlets.outletsid
      and `date_created` BETWEEN ('".$start_date."') AND   ('".$end_date."')";
Pravesh
  • 25
  • 5
  • What is the column type of `date_created`? – Shiva Sep 06 '19 at 06:42
  • date_created type is datetime – Pravesh Sep 06 '19 at 06:45
  • See, if this works - https://stackoverflow.com/questions/6119369/simple-datetime-sql-query ? – Shiva Sep 06 '19 at 06:46
  • my start and end date are not hardcore thats the problem how i could input the 00:00:00 in the start and 23:59:59 in the end date – Pravesh Sep 06 '19 at 06:55
  • Whatever programming language you are using to extract or parse `start_date/end_date` from frontend, use the same language to format the dates in the format as required by SQL(or specific RDBMS) – Shiva Sep 06 '19 at 07:01
  • its not working i had to input the format 00:00:00 in the backend for the report to run – Pravesh Sep 06 '19 at 07:06
  • 1
    What programming language and RDBMS are you using? Can you add snippets of the code where you are extracting/parsing the dates? – Shiva Sep 06 '19 at 07:11

1 Answers1

0

Just get some idea from the below script that How you should handle your start and end date.

SELECT 
CAST('2019-06-00' AS DATETIME),
CAST(DATE_ADD('2019-06-10', INTERVAL 1 DAY) AS DATETIME); 
/*Added 1 day with the end date so that you can use date < end_date*/

Your query will be something like-

....
WHERE
....
AND `date_created` >= CAST('2019-06-00' AS DATETIME)
AND `date_created` < CAST(DATE_ADD('2019-06-10', INTERVAL 1 DAY) AS DATETIME);

Still if you want to use BETWEEN, the script should be as below-

....
WHERE
....
AND `date_created` 
    BETWEEN CAST('2019-06-00' AS DATETIME)
    AND CAST(CONCAT('2019-06-10',' 23:59:59') AS DATETIME),
mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • I got the answer by myself I try this and It worked and `date_created` BETWEEN ('".$start_date." 00:00:00') AND ('".$end_date." 19:59:59')"; – Pravesh Sep 06 '19 at 10:09
  • Guess you mean 23:59:59. You can also use BETWEEN. But checking >= and < as I shown, is a better option and server your same purpose. – mkRabbani Sep 06 '19 at 10:11
  • @Pravesh please check the BETWEEN syntax I have added in the answer. – mkRabbani Sep 06 '19 at 10:16