Selecting records by date using DAYOFYEAR()

rudybob23 · #1 (**permalink**) 10-20-03, 12:53

Hi,
I'm not very well versed in mySQL but I've found myself using it every now and then. I need to select all records that have a date value within the next two weeks. What I've currently got is this:

select DATE_FORMAT(date, '%m\/\%d\/\%y'), location, time, id, numFree from refugee_clinical WHERE DAYOFYEAR(date) BETWEEN DAYOFYEAR(CURRENT_DATE) and (DAYOFYEAR(CURRENT_DATE)+14) order by date;";

This works, but I don't think that it will return the proper results from December 18th until January 1st as (CURRENT_DATE)+14 is going to be returning values above 365. Is there any easy way to get around this problem or should I just redesign the select statement using a different function?

Thanks in advance for any help!

-rudy

Mincer · #2 (**permalink**) 10-21-03, 10:37

This should do what you're after.

Code:

SELECT
    DATE_FORMAT( date, '%m\/\%d\/\%y' )
  , location
  , time
  , id
  , numFree
FROM
    refugee_clinical
WHERE
    TO_DAYS( date ) - TO_DAYS( CURDATE() ) BETWEEN 0 AND 13
ORDER BY
    date

Regards,

Matt.

BTW, I think your DATE_FORMAT() string is a bit messed up

Internet Services

Web Development

Digital Marketing

Consumer Tech