Extract year from date in MySQL database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ChristopherD
Forum Newbie
Posts: 4
Joined: Tue Nov 16, 2004 10:40 am

Extract year from date in MySQL database

Post 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?
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

look for Date_Format here
http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html
as it will help
ChristopherD
Forum Newbie
Posts: 4
Joined: Tue Nov 16, 2004 10:40 am

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

or

Code: Select all

SELECT YEAR(datethingie) AS year FROM foo
ChristopherD
Forum Newbie
Posts: 4
Joined: Tue Nov 16, 2004 10:40 am

Post 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.
ChristopherD
Forum Newbie
Posts: 4
Joined: Tue Nov 16, 2004 10:40 am

Post 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.
peni
Forum Commoner
Posts: 34
Joined: Thu Nov 18, 2004 1:15 pm

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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";
Post Reply