Retrieve 'Year' only from a MySql 'Date' field

PHP
1

Hi,

I have a field type ‘Date’ in my MySql table. What i want is when i retrieve information from a row, i want to be able to get only the Year from that date field. Or it could also be just the month or just the day. But the date in my date field must stay complete (YYYY-mm-dd).

Thanks

2

Take a look at the MySql Year() function

E.g.

SELECT YEAR(‘2005-11-30’);

3

Ok, it seams that it would be the function i need to retrieve the information. But how do i use it with php? How do i output the result?

4

Ok, i’ve found another solution that works perfect:

lets say that $var represent your value from the ‘Date’ field (YYYY-mm-dd).

This will output the year only:
echo date(‘Y’,strtotime($var));

Tha same for the Day or Month:
echo date(‘d’,strtotime($var)) OR echo date(‘m’,strtotime($var))

5

Yeah, that’ll work fine pal.

The way I mentioned just does it for you at database level.

$sql = "SELECT YEAR(yourtable.date) as year from yourtable;";
6

“at database level” is extremely important

suppose you wanted to filter rows based on the year, i.e. return only those rows for a specific year (for example, say the table has many rows for many different years)

then using the database YEAR() function in the WHERE clause becomes crucial

you do not want to return the entire table from the database to your php script, in order to use php’s date function to pick out the rows for the year you want

see the difference? :slight_smile:

7

Thanks a lot guys, I’ll make some test with the YEAR() function…

8

OK, i had problems with the strtotime function since it return the wrong date on year smaller than 1969…

So i tested : $sql = “SELECT YEAR(yourtable.date) as year from yourtable”;
and that work even better than the strtotime…Thanks a lot for your advice…

9