Search code examples
mysqlsqldatetimetimestamp

Select rows for a specific date using TIMESTAMP datatype

I have the tables below in my database:

Student    (Stud_no: string, Stud_name: string)
Membership (Mem_no: string, Stud_no: string)
Book       (book_no: string, book_name: string, author: string)
Iss_rec    (iss_no: integer, iss_date: date, Mem_no: string, book_no: string)

I created the tables and inserted values defined by the above schemas. I used TIMESTAMP in the iss_date so that present date is taken by the system automatically as the default value for iss_date.

Now I have to List all the student and Book name, Author issued on a specific date (e.g., 06-08-2019).

I wrote this query: 

SELECT student.stud_name, book.book_name, book.author,iss_rec.iss_date
FROM student, book, iss_rec, membership
WHERE student.stud_no = membership.stud_no
AND membership.Mem_no = iss_rec.Mem_no
AND book.book_no = iss_rec.book_no
AND iss_date = '2019-08-06'

but it yields nothing! Whenever I cut off the last line: AND iss_date = '2019-08-06', it yields a list. But how could I get list for a specific date as I mentioned earlier? I don't want hour:minute:second, only yyyy-mm-dd.

Solution

  • You can use DATE function

    AND DATE(iss_date) = '2019-08-06';