/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] MySQL select based on current date in date field

Copy linkTweet thisAlerts:
Jul 22.2015

Hello all,

I am trying to automate an email for our company that will alert upper management of upcoming employment anniversary dates. I am stuck on my query. The field “hire_date” is a date field (not datetime or timestamp). I would like for my query to search the “hire_date” field for any anniversary dates within 14 days.

I have done some research and understand that the “date” field type may not best be suited for this type of query. Therefore, I have two questions:

  • 1. What repercussions will I experience should I change the field type (which field type would you recommend)?

  • 2. If this query CAN be done with the current field type, how would I go about doing this?

    • My attempt at this query is:

    [code=php]“SELECT * FROM pto.employees WHERE hire_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY)”)[/code]

    Of course, this doesn’t work as the date field includes the year, which this query takes into account.

    The database I am using is already in use on another project, so I’d like to minimize any risk associated with changing the field type.

    Thank you in advance for your help.

    to post a comment
    PHP

    2 Comments(s) ↴

    Copy linkTweet thisAlerts:
    @iBeZiJul 22.2015 — Not sure if this is the best way of doing it, but it's the quickest way I can think of for now

    [code=php]SELECT
    *
    FROM
    pto.employees
    WHERE
    concat(MONTH(hire_date),'-',DAY(hire_date)) > concat(MONTH(NOW()),'-',DAY(NOW()))
    AND
    concat(MONTH(hire_date),'-',DAY(hire_date)) < concat(MONTH(DATE_ADD(NOW(), INTERVAL 7 DAY)),'-',DAY(DATE_ADD(NOW(), INTERVAL 7 DAY)))[/code]

    Edit: Forgot about DATE_FORMAT

    [code=php]SELECT
    *
    FROM
    pto.employees
    WHERE
    DATE_FORMAT(hire_date,'%m-%d') > DATE_FORMAT(NOW(),'%m-%d')
    AND
    DATE_FORMAT(hire_date,'%m-%d') < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 7 DAY),'%m-%d')[/code]
    Copy linkTweet thisAlerts:
    @TW79authorJul 22.2015 — That did the trick. Thank you very much for your help.
    ×

    Success!

    Help @TW79 spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 5.25,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @AriseFacilitySolutions09,
    tipped: article
    amount: 1000 SATS,

    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,
    )...