hi,

i need to concat two columns aswell as perform a time_format on them, struggling with syntax, heres what i have:

SELECT CONCAT(stf.forename, ' ', stf.surname) AS Name, DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date,
TIME_FORMAT(CONCAT(shf.start_time, '-', shf.end_time, '%H - %i %p')) as Shift
FROM staff stf, shift shf, staff_shift ss
WHERE shf.shiftID = ss.shiftID

im getting the error

"Incorrect parameter count in the call to native function 'TIME_FORMAT'"

returned

please help!!!

Have a look at your second TIME_FORMAT function:

SELECT CONCAT(stf.forename, ' ', stf.surname) AS Name, DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date,
TIME_FORMAT(CONCAT(shf.start_time, '-', shf.end_time), '%H - %i %p') as Shift
FROM staff stf, shift shf, staff_shift ss
WHERE shf.shiftID = ss.shiftID

thanks for the reply!

i've got a result, which is great however the two 'times' are failing to concatenate in the column

what datatypes are the start_time and end_time columns? could we see sample values?

the 'start_time and end_time are both 'TIME' datatypes, example entries would be 09:00:00 and 21:00:00

oh, i see

could you please explain what you were trying to do here...

the two columns need to be concatenated, its to display a shift for someone. Rather than have it displayed as 'start_time 09:00:00' as one column and 'finish_time 21:00:00' as another, i thought it would be be better for the end user user to be able to read it as just 'Shift 9:00 am - 9:00pm' (24 hour format is a possible alternative)

okay, i understand

try this --

r937, ever a legend!! it works! thank you so much!!

just to tie it up there was one bracket missing..

SELECT CONCAT(stf.forename,' ',stf.surname) AS Name
, DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date
, CONCAT(TIME_FORMAT(shf.start_time,'%h:%i %p')
,' - '
,TIME_FORMAT((shf.end_time),'%h:%i %p')
) AS Shift