Page 1 of 1

Extract year from date in MySQL database

Posted: Tue Nov 16, 2004 10:46 am
by ChristopherD
I have dates stored in a MySQL database in the format YYYY-MM-DD. I need to extract just the year and store it in a PHP variable. How would I do that?

Posted: Tue Nov 16, 2004 11:07 am
by phpScott
look for Date_Format here
http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html
as it will help

Posted: Tue Nov 16, 2004 11:54 am
by ChristopherD
phpScott,

Thanks for the pointer. I ran across that page before I posted, but it didn't seem to have what I needed. Looking through it again it does look like the SELECT DATE_FORMAT('YYYY-MM-DD', '%X'); would do what I need.

The one thing I am unclear about, though, is how I would insert the YYYY-MM-DD data from the database cell into the SELECT DATE_FORMAT function. I'm new to PHP and MySQL (as if it isn't obvious), and I don't neccessarily know steps that others more experienced than I would automatically take for granted.

I do know how to retrieve data from a cell and assign it to a PHP variable, but I'm not sure how/where the SELECT DATE_FORMAT function fits into the whole process.

Posted: Wed Nov 17, 2004 3:22 am
by phpScott

Code: Select all

SELECT DATE_FORMAT(fieldNameWithDate,'%Y')AS year FROM tableName
then use year where you would normaly use the column name to extract the value from your query.
You can add your where clause or anything else you need to get the right year value.

Posted: Wed Nov 17, 2004 4:49 am
by timvw
or

Code: Select all

SELECT YEAR(datethingie) AS year FROM foo

Posted: Wed Nov 17, 2004 3:41 pm
by ChristopherD
Thanks! I'm leaving on a trip for a few days, so I'll try this out when I get back. I sure appreciate the help.

Posted: Tue Nov 23, 2004 1:01 pm
by ChristopherD
Well, I must be doing something wrong. I tried both examples, and kept getting "Parse error: parse error, unexpected T_STRING."

I tried this:

Code: Select all

<?php 
								
								$year = SELECT YEAR('%Y')AS year FROM $Videos->Value("DateFinished");
								
								echo "$year";
								
								?>
and this:

Code: Select all

<?php 
								
								$year = $Videos->Value(SELECT DATE_FORMAT(DateFinished,'%Y')AS year FROM $Videos);
								
								echo "$year";
								
								?>
and this:

Code: Select all

<?php 
								
								$year = SELECT DATE_FORMAT(DateFinished,'%Y')AS year FROM $Videos);
								
								echo "$year";
								
								?>
I used the "$TableName->Value("FieldName")" code because I'm using GoLive 6 with the dynamic content module, and with this software this is the code used to access the field name you want from the current record being displayed. I've used this successfully with one other script I wrote.

Posted: Tue Nov 23, 2004 1:48 pm
by peni
i remember i had sth like

Code: Select all

SELECT LEFT(`x_date`, 4) AS yyyy FROM news
some weeks ago to extract the year from yyyy-mm-dd

Posted: Tue Nov 23, 2004 4:23 pm
by timvw
ChristopherD wrote:Well, I must be doing something wrong. I tried both examples, and kept getting "Parse error: parse error, unexpected T_STRING."
RTFM http://www.php.net/language.types.string

Code: Select all

echo " {$TableName->Value("fieldName")} other stuff";