douzhang5295
douzhang5295
2014-07-29 18:53

MYSQL使用LIKE函数查询日期

已采纳

I have the following statement in the where statement of a mysql query:

WHERE scrap_date LIKE '%-01-%'

I want to grab all the data with the scrap_date being in January. The error I recieve is:

"Incorrect datetime value: '%-01-%' for column 'scrap_date' at row 1"

The datatype for scrap_date is DATETIME. Not sure what syntax to use to get data with the a date in January, any suggestions?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • duanci9305 duanci9305 7年前

    You are assuming the date is represented internally as a string. It is not.

    Since its a DateTime, use the MONTH function to extract the month and compare it to the desired value

    WHERE MONTH(scrap_date) = 1
    
    点赞 评论 复制链接分享
  • dongqiulei6805 dongqiulei6805 7年前

    use month function

    WHERE MONTH(scrap_date) = 1
    
    点赞 评论 复制链接分享
  • douxiduan8344 douxiduan8344 7年前

    You can use the DATEPART() function

    SELECT * FROM table
    WHERE  (DATEPART(mm, scrap_date) = 01)
    
    点赞 评论 复制链接分享
  • douping7975 douping7975 7年前

    You may try this:

    select  * from your table WHERE MONTH(scrap_date) = 1
    
    点赞 评论 复制链接分享