Date and time formatting and manipulation

My customer is always after more data within specific date ranges.   My customer wants a start and end date for each student in our moodle instance.

Get and format the *start date for each student

Select from_unixtime(ra.timemodified, '%m/%d/%y') as date_enrolled

The more tricky part is adding 20 weeks to the enroll date to project a completion date and format it so it is easy to compare the start and end dates.

date_format (date_add (from_unixtime(ra.timemodified,'%y/%m/%d', interval 20 week) , '%m/%d/%y')) as projected_completion_date.

This formula was very tricky because the date_add function requires the field you are adding to to be in a specific format y/m/d for the inverval 20 week to work.  I actually did that first then added the date_format to the mix like this:

Get the desired result first ....
date_add (from_unixtime(ra.timemodified,'%y/%m/%d'), interval 20 week)

Then added the date_format around the expression, like

Then add the date formatting ...
date_format( date_add(from_unixtime(ra.timemodified, '%y/%m/%d')   , interval 20 week) , '%m/%d/%y') as projected_completion_date

This is the complete sql and what the output looks like.

SELECT u.firstname, u.lastname, c.fullname,
from_unixtime(ra.timemodified, '%m/%d/%y') as date_enrolled,
date_format( date_add(from_unixtime(ra.timemodified, '%y/%m/%d')   , interval 20 week) , '%m/%d/%y') as projected_completion_date

FROM
mdl_role_assignments ra
join mdl_user u on u.id = ra.userid
join mdl_context ct on ct.id = ra.contextid
join mdl_course c on c.id = ct.instanceid

where roleid = 5 and from_unixtime(u.timecreated, '%m/%d/%y') > '08/08/12'
order by ra.timemodified

Formatted output from sql