date language in ad-hoc database queries

date language in ad-hoc database queries

by Helen Moraes -
Number of replies: 1

Hi,

I hope this is the correct forum.

I use "Ad-hoc database queries" plugin and run this one, to get  course completion report with dates:

SELECT u.username AS 'User Name',
CONCAT(u.firstname , ' ' , u.lastname) AS 'Name',
c.shortname AS 'Course Name', 
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%W %e %M, %Y') AS 'Completed Date',
ROUND(c4.gradefinal,2) AS 'Score'
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_course_completion_crit_compl AS c4 ON u.id = c4.userid
WHERE c.enablecompletion = 1  AND (p.timecompleted IS NOT NULL OR p.timecompleted !='') 
AND (p.timecompleted>= :start_date AND p.timecompleted<=:end_date)
GROUP BY u.username
ORDER BY c.shortname

It works, but the date generated in CSV is in english language and not in mine. I checked all lang settings within Moodle and everything is ok (no setting is "english")

How can I tell to generate a correct language CSV report?

 (I use Moodle 2.6.)

Thanks.

Average of ratings: -
In reply to Helen Moraes

Re: date language in ad-hoc database queries

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Hi,

I'm not sur it answers the question, but you can just modify the line containing the DATE_FORMAT command, to ajust the format of the date like you want. Have a look at the DATE_FORMAT documentation smile

So you could (for example) change the line to :

DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%Y/%m/%d') AS 'Completed Date',

HTH,
Séverin

Average of ratings: -