CODEWITHSUNDEEP
mysql
user1481793
user1481793

Reputation: 553

Mysql filter data by current date

I am having a table like..

    | id | name             | meetingID | meeting_type | status | recurring_time | attendee_passwd | moderator_passwd | date_created        | timezone  | meeting_datetime    | reminder | duration | created_by_id | sms_no | conferenceID | meeting_logout_url | max_participants | participants_id |
    +----+------------------+-----------+--------------+--------+----------------+-----------------+------------------+---------------------+-----------+---------------------+----------+----------+---------------+--------+--------------+--------------------+------------------+-----------------+
|  7 | 6august          | REG_524   |              |   NULL |                | 90200           | 18614            | 0000-00-00 00:00:00 | GMT  5.45 | 2012-08-06 03:00:00 |        0 | 60       |             1 |  12356 |         NULL | http://log.com     |               20 |            NULL |

I am trying to filter the table by current date so for i am using the following query 

 SELECT *  from demo_meeting WHERE created_by_id IN(SELECT id FROM demo_user WHERE user_name = ' Sang') and meeting_datetime=curdate();

But i am getting the empty set instead of getting the result.

please tell me what i am doing wrong here

Upvotes: 3

Views: 9276

Answers (4)

Amrita
Amrita

Reputation: 611

you are comparing curdate() with the field meeting_datetime which is having datatype datetime/timestamp; what you exactly need to do is compare like date(meeting_datetime) with curdate().

query will be: SELECT * from demo_meeting WHERE created_by_id IN(SELECT id FROM demo_user WHERE user_name = ' Sang') and date(meeting_datetime)=curdate();

Hope it will help you out.

Upvotes: 0

John Woo
John Woo

Reputation: 263933

try to format your date using DATE( ) function. BTW, joining of tables is much more efficient than SubQuery

SELECT  a.*
FROM    demo_meeting a 
            INNER JOIN demo_user b
                ON a.created_by_id = b.ID
WHERE   b.user_name = 'Sang' AND
        DATE(a.meeting_datetime) = DATE(curdate())

Upvotes: 1

arnoudhgz
arnoudhgz

Reputation: 1284

SELECT *  from demo_meeting WHERE created_by_id IN(SELECT id FROM demo_user WHERE user_name = ' Sang') and DATE_FORMAT(meeting_datetime, '%Y-%m-%d')=curdate();

Upvotes: 1

ARRG
ARRG

Reputation: 2496

You are comparing a datetime with a date.

Try casting your datetime to a date, like this: 

SELECT *  from demo_meeting WHERE created_by_id IN ( SELECT id FROM demo_user WHERE user_name = ' Sang') AND DATE(meeting_datetime) = curdate();

Upvotes: 8

Related Questions