group by with correct date format

Applications Grid / Reports
#1

I’m using a group by in a grid.

the group by is based on a field and i use concat to show mare field values in the group by line

SELECT sc_concat("<h6><font color = ‘#ffffff’>", ronde_naam, " - “, ronde_start_tijd,” - ", ronde_eind_tijd, " - ", ronde_datum, “</font></h6>”)
FROM event_ronde
WHERE ronde_id = {event_workshop.ronde_id}

works fine, but date and time show all wrong. time with seconds and date in the wrong format.

so i tried:

SELECT sc_concat("<h6><font color = ‘#ffffff’>", ronde_naam, " - “, ronde_start_tijd,” - ", ronde_eind_tijd, " - ", DATE_FORMAT(ronde_datum , ‘%d/%m/%Y’), “</font></h6>”)
FROM event_ronde
WHERE ronde_id = {event_workshop.ronde_id}

that gives an error.

any suggestions?

#2

Try changing at date:

DATE_FORMAT(ronde_datum,’%d.%m.%Y’) as ronde_datum,

#3

He Rik,

thx for the answer.
this supposed to be working but i get the following error

") FROM event_ronde WHERE ronde_id = {event_workshop.ronde_id} (Incorrect parameter count in the call to native function ‘DATE_FORMAT’)

when i use this in the lookup

SELECT sc_concat("<h6><font color = ‘#ffffff’>", ronde_naam, " - “, ronde_start_tijd,” - ", ronde_eind_tijd, " - ", DATE_FORMAT(ronde_datum,’%d.%m.%Y’) as ronde_datum, “</font></h6>”)
FROM event_ronde
WHERE ronde_id = {event_workshop.ronde_id}

#4

Drop the [SIZE=24px][/SIZE] in the sql DATE_FORMAT([U][SIZE=22px][/SIZE][/U]ronde_datum[SIZE=22px]`[/SIZE],’%d.%m.%Y’)

#5

i don’t now what you mean with that :rolleyes:

#6

Je gebruikt aanhalingstekens in
DATE_FORMAT([/U]ronde_datum[U],’%d.%m.%Y’)

Gelet dit een veld is uit een tabel mag je deze NIET gebruiken. De aanhalingstekens voor en na ronde_datum moet weg.
DATE_FORMAT(ronde_datum,’%d.%m.%Y’)

#7

yeah that should work… but it doesn’t :mad:

#8

drop the “as ronde_datum” after your DATE_FORMAT