date query problems

[ripchen]

I have a field in a MySQL table for hiredate (the date only no time). I need to have a query that will find anyone hired this week regardless of the year, basically the anniversary of the being hired. For example right now this week is 29AUG through 4SEP.

Can't seem to find the get the expected resuts particuary a week liket his that spans two months.

This is probably easy but I'm missing it

Thanks

[zhabala]

Try to play with date functions:

DAYOFMONTH(date)
Returns the day of the month for date, in the range 1 to 31:
mysql> SELECT DAYOFMONTH('1998-02-03');
-> 3

DAY(date)
DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1.

MONTH(date)
Returns the month for date, in the range 1 to 12:
mysql> SELECT MONTH('1998-02-03');
-> 2

Zdenek

[ripchen]

Thanks. I have read about the date fucntions and have used them to determine the month and date as you show.

Where I have the problem is how to build the query that once I know the month and date taken from the hiredate field, how do I then correctly determine if that hiredate is between last Sunday and this coming Saturday which I know from using other date functions e.g. CURDATE() and DAYOFWEEK()

[zhabala]

I don't know exactly, what you mean, but when you are able to determine the date for last Sunday and comming Saturday then you can do something like

SELECT * FROM TABLE where
DATE_FORMAT(hiredate,'%m') || DATE_FORMAT(hiredate,'%d') >= '0829' and DATE_FORMAT(hiredate,'%m') || DATE_FORMAT(hiredate,'%d') <= '0904'

Zdenek

[ripchen]

Thanks that got me close.

I made a small change and it works.

SELECT *
FROM TABLE
WHERE CONCAT( DATE_FORMAT( hiredate, '%m' ) , DATE_FORMAT( hiredate, '%d' ) ) >= '0829' AND CONCAT( DATE_FORMAT( hiredate, '%m' ) , DATE_FORMAT( hiredate, '%d' ) ) <= '0904'_LIMIT 0 , 30