Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: Selecting records by date using DAYOFYEAR()

    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

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •