CODEWITHSUNDEEP
mysqldatetime
chongzixin
chongzixin

Reputation: 1981

MySQL check if datetime column is within weekend period

I have a table with a datetime column in the database. Is there a way i can write my query to determine if the record is within the weekend period?

I want to display all records and have an additional column in each result row to display Weekend if it meets the criteria of (after Friday 1730hours) or (before sunday 2359hours).

The below is what I currently have but something seems to be wrong. :( any help will be greatly appreciated. :)

SELECT recordTime, DATE_FORMAT(recordTime, "%W %w %T"), (( (DATE_FORMAT(recordTime, "%T") > '17:30:00' AND DAYOFWEEK(recordTime)=6)
    AND (DATE_FORMAT(recordTime, "%T") < '23:59:59' AND DAYOFWEEK(recordTime)=7) )
    OR (DATE_FORMAT(recordTime, "%T") < '23:59:59' AND DAYOFWEEK(recordTime)=0)) AS Weekend
FROM `waitrecord`

Upvotes: 8

Views: 27491

Answers (1)

Alex Monthy
Alex Monthy

Reputation: 1877

Your logic is wrong. A day cannot be weekday 6 and weekday 7 at the same time.

The condition should be

DAYOFWEEK(recordTime) = 7
or DAYOFWEEK(recordTime) = 1
or (DATE_FORMAT(recordTime, "%T") > '17:30:00' AND DAYOFWEEK(recordTime) = 6)

Edit: DAYOFWEEK actually returns 1 for Sunday and 7 for Saturday (in contrast to DATE_FORMAT which is zero based)

Upvotes: 24

Related Questions