What is the nicest way to select the current week in MySQL

Question

At the moment I have something like this...

SELECT SUM(a.PaidSingle) AS PaidSingle,
       DATE_FORMAT(a.TimeIn, '%a') AS weekDay
FROM Attendance AS a JOIN MemberDetail AS m ON m.id = a.MemberID
WHERE m.CardNumber = '$cardNo'
AND   WEEK(a.TimeIn, 0) = WEEK(NOW(),0)
GROUP BY weekDay
ORDER BY a.TimeIn

But it dawned on me after writing it that this will take things from previous years as well as the current year.

I wanted to simply then say and year = thisYear but of course then on a week starting in December and ending in January I will only get half a week.

What is the best way to do this in MySQL or should I use a PHP based solution?

Answer 1

why not select the weekday:

SELET DATE_FORMAT(NOW(), '%u')

as described here

Answer 2

You're using WEEK with mode=0, which returns week numbers from 0-53 - that leads to a problem around new year, where part of the week might be 'YYYY53' and the rest 'YYYZ00' if you will.

If you could use mode 2 that would be compatible with the DATE_FORMAT %V and %X options:

DATE_FORMAT( NOW( ), "%X%V" )

These only use week numbers from 1-53, hence it's possible to get a week YYYY53 event for a date in year YYYZ.

Doesn't meet your requirement for niceness I'm afraid!