Forum Moderators: open
I have come up with the following queries which are working for me. Please see it and let me know if i can enhance the logic
Only today's records
WHERE visiTime < date_sub(curdate(),interval 0 day)
Only Yesterday and not today
WHERE visiTime BETWEEN date_sub(curdate(),interval 1 day) AND date_sub(curdate(),interval 0 day)
This Week starting from Monday to current weekday
WHERE visiTime BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY ) AND CURDATE( )
Only this month from its starting day, NOT 30 days from today.
where visiTime
>= date_sub(current_date
, interval dayofmonth(current_date)-1 day)
and visiTime
< date_add(
date_sub(current_date
, interval dayofmonth(current_date)-1 day)
, interval 1 month)
Only last month and NOT THIS MONTH
WHERE month(visiTime) = month(date_add(now(),interval -1 month))
Only last year and NOT THIS YEAR
WHERE year(visiTime) = year(date_add(now(),interval -1 year))
I know expert mysql admins are very busy people but please please share your thoughts on this. thank you
Only today's records:
WHERE visiTime = CURRENT_DATE
A couple comments:
If you want a future date, use date_add. A past date, date_sub. When you do date_add(curdate(),-1 year) you get into a double-negative confusion. :-)
Second, curdate() is a date only format:
0000-00-00
now() is a datetime format:
0000-00-00 00:00:00
Although the date math will work on either, I always say "right tool for the job." If you don't need a time, use curdate().
Only today's records
Only Yesterday and not today
The rest I don't have time for this AM, others will assist!
current_date() is synonymous with curdate(); :-)
In MySQL, yes, but the standard is CURRENT_DATE (no parenthesis). It is a "niladic" or "nullary" datetime function. I believe CURDATE originated with ODBC and was replaceable with standard SQL3. Anyway, the point I was making was that there was no need for the mathematical expression using an interval when a comparison expression would suffice.
To take it a step further, why not write all the clauses using standard SQL rather than MySQL-specific functions? Makes the code portable ... just a thought.
WHERE month(visiTime) = month(date_add(now(),interval -1 month))
this will return the records of april 2005, april 2006, april 2008 april 2009 and so on.
i am not sure if he is right or not and unfortunately i didn't get solution from him.
Only last month and NOT THIS MONTHWHERE month(visiTime) = month(date_add(now(),interval -1 month))
If so, you need to add the year to that query. Compare year/month rather than just month.
