Extract year from date in MySQL database
Moderator: General Moderators
-
ChristopherD
- Forum Newbie
- Posts: 4
- Joined: Tue Nov 16, 2004 10:40 am
Extract year from date in MySQL database
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?
-
ChristopherD
- Forum Newbie
- Posts: 4
- Joined: Tue Nov 16, 2004 10:40 am
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.
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.
Code: Select all
SELECT DATE_FORMAT(fieldNameWithDate,'%Y')AS year FROM tableNameYou can add your where clause or anything else you need to get the right year value.
or
Code: Select all
SELECT YEAR(datethingie) AS year FROM foo-
ChristopherD
- Forum Newbie
- Posts: 4
- Joined: Tue Nov 16, 2004 10:40 am
-
ChristopherD
- Forum Newbie
- Posts: 4
- Joined: Tue Nov 16, 2004 10:40 am
Well, I must be doing something wrong. I tried both examples, and kept getting "Parse error: parse error, unexpected T_STRING."
I tried this:
and this:
and this:
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.
I tried this:
Code: Select all
<?php
$year = SELECT YEAR('%Y')AS year FROM $Videos->Value("DateFinished");
echo "$year";
?>Code: Select all
<?php
$year = $Videos->Value(SELECT DATE_FORMAT(DateFinished,'%Y')AS year FROM $Videos);
echo "$year";
?>Code: Select all
<?php
$year = SELECT DATE_FORMAT(DateFinished,'%Y')AS year FROM $Videos);
echo "$year";
?>i remember i had sth like some weeks ago to extract the year from yyyy-mm-dd
Code: Select all
SELECT LEFT(`x_date`, 4) AS yyyy FROM newsRTFM http://www.php.net/language.types.stringChristopherD wrote:Well, I must be doing something wrong. I tried both examples, and kept getting "Parse error: parse error, unexpected T_STRING."
Code: Select all
echo " {$TableName->Value("fieldName")} other stuff";