Date format - sectioning query

[sazzie]

Hi,
I would like to know if there is a way to extract just the year from a date in a mysql database?

So, if I had a date in my database saved as 2006-07-05, can I extract just the 2006 in my sql query string :

ie - SELECT * FROM startDates WHERE ... this is where I want to check for 2006 ???

Please help  :)

[paul2463]

yes there is have a look at <a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html">DATE_FORMAT()</a>

[code]
$query = "SELECT DATE_FORMAT(`date`,%Y) FROM `table";
[/code]

[sazzie]

I tried doing the following code in php:

[code]
SELECT * FROM startDates WHERE to_date = 'stristr(date_format(to_date,'%Y'), "2006" ';
[/code]

And that did not work. You see, I am trying to not only extract the year but to match it to 2006 in the query string
so only specific entries are returned.

[paul2463]

try
[code]
SELECT * FROM startDates WHERE DATE_FORMAT(to_date,"%Y") =  "2006" ';
[/code]

[sazzie]

Thank you very much. I can see the logic in what you have done there. :)

Just one last thing. I tried integrating you code, can you spot the syntax error :

[code]
$sql_show = "SELECT  event_ref, b.event_title, date_format(from_date, '%d/%m/%Y') as date_from, date_format(to_date, '%d/%m/%Y') as date_to, duration1, duration2, provider FROM ucl_learning_event a,  ucl_events b where a.event_title = b.id  and emp_no = '".$eNum."' and DATE_FORMAT(to_date,'".%Y."') = '2006' and da_add = '0' order by from_date";
[/code]
???

[paul2463]

try this, I havnt done much, just removed some of the speech marks from the query and capitalised the sql commands to make it ewasier to read
[code]
$sql_show="SELECT event_ref, b.event_title, DATE_FORMAT(from_date, '%d/%m/%Y') AS date_from, DATE_FORMAT(to_date,  '%d/%m/%Y') AS date_to, duration1, duration2, provider FROM ucl_learning_event a, ucl_events b WHERE a.event_title=b.id AND emp_no = '$eNum' AND DATE_FORMAT(to_date,'%Y') = '2006' AND da_add = 0 ORDER BY from_date";
[/code]

[sazzie]

Thank you very much for everything.

I appreciate all your help.

Things look fine now.

Thanks again  ;D

[fenway]

Or use the YEAR() function.