Select day from date field in SQL table

Posted on 2006-05-14
Last Modified: 2008-02-26

I have a date field in my SQL table
I have tried this and it works perfectly.

sql = "SELECT * FROM mytable WHERE MONTH(mydate)=3 AND YEAR(mydate)=2005"

But when I try this, i tells me there is a syntax error.  Why?  How to rectify?

sql = "SELECT * FROM mytable WHERE DAY(my_date)= 27"

I'm totally puzzled since I read from tutorials that you can use the DAY function to rerieve the day from date.
Question by:limhodav
    LVL 142

    Accepted Solution

    What version of MySQL do you use vs which version of the documentation did you read?
    Day() It is available as of MySQL 4.1.1.

    you might try to use DAYOFMONTH(date) instead

    Author Comment

    Thanks a million. I used DayofMonth and it worked immediately.!!!! Have been wrecking my brains so hard for a solution and it's actually that simple.  Didn't know bout the existence of this function.

    Btw, I'm using SQL version 3.2. Perhaps, the version is too low to be able to use the date() function.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Btw, I'm using SQL version 3.2. Perhaps, the version is too low to be able to use the date() function.

    as noted:
    Day() It is available as of MySQL 4.1.1.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now