Can you use substr in a MySQL query?

[galvin]

I have a MySQL table with a "dob" field that stores specific dates and times.  The data is in this format, for example...

 

2011-02-03 14:08:00

 

I want to retrieve the unique YEAR values, as efficiently as possible. I came up with the code below which doesn't work. So my assumption is that you are not able to use "substr" within a MySQL query. Is that right?

 

If so, can someone guide in the direction of the best way to achieve this.  I guess I have to just bring back all the dob FULL values into an array, but then not sure how to get just the year (first four characters) and only the UNIQUE ones, since many will be the same year.

 

Or if you actually CAN use "substr" within a MySQL query, then I must have screwed something else up, so let me know what else I might have done wrong.

 

 

$sql = "SELECT distinct substr(dob,0,4) AS year FROM info
WHERE id = '{$_GET['id']}'";
$getyear = mysql_query($sql, $connection);
if (!$getyear) {
die("Database query failed: " . mysql_error());
} else {
	while ($years = mysql_fetch_array($getyear)) {
		echo "Years=" . $years['year'];
	}
}

 

[Pikachu2000]

MySQL has a DATE_FORMAT() function.

[galvin]

Thanks Pikachu2000.  I apologize for being a bigtime newbie with MySQL functions but can I stick that function IN the query, or do I call it somewhere else?

 

I tried this, which is obviously not correct :-\

 

$sql = "SELECT distinct DATE_FORMAT('dob','%Y') AS year FROM data
WHERE id = '{$_GET['id']}'";
$getyear = mysql_query($sql, $connection);
if (!$getyear) {
die("Database query failed: " . mysql_error());
} else {
	while ($years = mysql_fetch_array($getyear)) {
		echo "Years=" . $years['year'];
	}
}

 

[Pikachu2000]

Change the single quotes around dob to `backticks`. You may also need to use parentheses for the DISTINCT function.

$sql = "SELECT DISTINCT( DATE_FORMAT(`dob`,'%Y') ) AS year FROM data WHERE id = '{$_GET['id']}'";

[PFMaBiSmAd]

DISTINCT is not a function. DISTINCT() just means that you have put a couple of extra () in your query.

[Pikachu2000]

OK, thanks for clearing that up.

[galvin]

The backticks did it!  Thanks so much!