extract year, month and date from mysql date field

Posted on 2005-04-09
Last Modified: 2013-12-12
Hi, I've got a mysql field called birthday of type date : It seems to display in the form YYYY-MM-DD. Now I want to extract the year, month and date and store them to seperate variables

//get from database and store in $birthday

$date = getdate ($birthday);

Now for some reason $year always displays 1970, although $birthday shows the proper date.
Question by:skylabel
    LVL 4

    Assisted Solution

    Hi skylabel,

    Try using this instead:
    list($year, $month, $day) = split('-', $date);
    echo "Month: $month; Day: $day; Year: $year<br />\n";

    LVL 32

    Accepted Solution

    $res = mysql_query("SELECT EXTRACT(YEAR FROM birthday) AS birthyear, EXTRACT(MONTH FROM birthday) AS birthmonth, EXTRACT(DAY FROM birthday) AS birthdate FROM tablename") or die("Sql error: " . mysql_error());

    while($row = mysql_fetch_array($res)) {
     echo $row["birthyear"] . "<br/>";
     echo $row["birthmonth"] . "<br/>";
     echo $row["birthdate"] . "<br/>";
    LVL 32

    Expert Comment

    getdate retrives the date information of the TIMESTAMP, it won't consider for DATE/DATETIME formats.

    This is another option:

    $birthday = $row["birthday"];
    echo date("Y", $birthday);
    LVL 32

    Expert Comment

    Or this:

    $date = getdate(strtotime($row["birthday"]));
    $year = $date['year'];
    LVL 9

    Assisted Solution

    You could also use the following SQL to get the dayofmonth, month and year
    select dayofmonth(yourdate) as dday, month(yourdate) as dmonth, year(yourdate) as dyear, ...........

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to count occurrences of each item in an array.

    758 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

    13 Experts available now in Live!

    Get 1:1 Help Now