dongrunying7537 2014-08-20 11:43
浏览 34
已采纳

Mysql日期字段搜索使用几个月来查找即将到来的生日

I am trying to fins 'upcoming birthdays' of the users present in the Database.

I can able to fine the users upcoming birthday only till 12 month (Dec).

What if I need to find the upcoming birthday of next year.

My Query:

SELECT c.F_CONTACT_ID,
       c.F_CONTACT_NAME,
       c.F_CONTACT_FNAME,
       DATE_FORMAT(c.F_DOB, '%d/%m') F_DOB
FROM t_contact c
WHERE DATE_FORMAT(F_DOB, '%m') >= '08'
GROUP BY c.F_CONTACT_ID
ORDER BY c.F_DOB ASC LIMIT 0 ,
                           10

How can I find the upcoming birthday of the users who born on 'January' because the

DATE_FORMAT(F_DOB, '%m') >= '08'

can able to check till 12th month.

Can some one help me?

  • 写回答

1条回答 默认 最新

  • douxuan3095 2014-08-20 11:46
    关注

    I have no idea why you have a group by. I would do this with an order by and limit:

    SELECT c.F_CONTACT_ID,
           c.F_CONTACT_NAME,
           c.F_CONTACT_FNAME,
           DATE_FORMAT(c.F_DOB, '%d/%m') F_DOB
    FROM t_contact c
    ORDER BY (case when month(c.F_DOB) >= '08' then month(c.F_DOB)
                   else month(c.F_DOB) + 12
              end)
    LIMIT 0, 10;
    

    That is, add "12" to the month numbers for next year. Note: this doesn't take the day of the month into account because your original query does not.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题