3

I'm trying to select from table Documents where Date is Today's date. I'm using Codeigniter. But it shows only these documents where there isn't hour. I mean where in database date is: 2015-06-25 00:00:00. But it doesn't display these documents where there is an hour - for example : 2015-06-25 08:34:00. How to display all documents where Date is current date , no matter what is the hour. 

<?php
 $date = new DateTime("now");
 
 $curr_date = $date->format('Y-m-d ');

 $this->db->select('*');
 $this->db->from('documents'); 
 $this->db->where('Date', $curr_date);
 $query = $this->db->get();
    return $query->result();

Improve this question
1
  • 2
    Assume your Date is the field name- that's bad practice to start with. You could try where('DATE(Date)', $curr_date) to ignore the time component. Not sure how code igniter works with MySQL functions but worth a go
    – scrowler
    Commented Jun 25, 2015 at 10:37

1 Answer 1

Reset to default
13

Try this

<?php
 $date = new DateTime("now");

 $curr_date = $date->format('Y-m-d ');

 $this->db->select('*');
 $this->db->from('documents'); 
 $this->db->where('DATE(Date)',$curr_date);//use date function
 $query = $this->db->get();
    return $query->result();

MySQL date Function

Improve this answer
5
  • Don't use DATE function, as it will call it for every single record. Doing between like query is the way to go, i.e. Date >= 'X 00:00:00' AND Date < 'X+1 day 00:00:00'
    – Soul Reaver
    Commented Nov 10, 2020 at 11:33
  • @SoulReaver Read the question before you post something. +When searching the exact day is more efficient using the WHERE clause rather than LIKE.
    – Abdulla Nilam
    Commented Nov 11, 2020 at 6:48
  • I had WHERE clause in mind all the time. I've meant beetween-like query not LIKE query.
    – Soul Reaver
    Commented Nov 12, 2020 at 7:24
  • @SoulReaver if its DateTime column BETWEEN must use. If DATE column no use of BETWEEN
    – Abdulla Nilam
    Commented Nov 12, 2020 at 20:56
  • Let me rephrase that. I had in mind a query like BETWEEN, i.e. one that uses < and => (which I already wrote in my very first comment) and not a query that uses BETWEEN or LIKE. Not my fault that query language uses actual English words.
    – Soul Reaver
    Commented Nov 18, 2020 at 11:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.