Query Change?

[atd]

I'm not sure when this started happening, but all of my queries that have DATE_FORMAT or TIME_FORMAT are coming a back with "System.Byte[]" as the value in those columns. Any ideas?
I'm on version 7.4.12.

[jordansparks]

Yeah. CHAR(DATE_FORMAT())

[atd]

Hmmm, that didn't work for me. I still get System.Byte[].

I'm trying to just show the appointment time in the column, not the date.
I used this for the column in my select statement:
TIME_FORMAT(apt.AptDateTime,'%h %i %p) as 'Time'

Changing it to this didn't help:
CHAR(TIME_FORMAT(apt.AptDateTime,'%h %i %p)) as 'Time'

[jordansparks]

Sorry.
DATE(DATE_FORMAT())

[atd]

But my query is for TIME_FORMAT, not DATE_FORMAT. I tried putting both DATE() and TIME() around it and neither gives me the result I want (although the System.Byte[] is gone).

[jsalmon]

Typically the DATE() will fix the issue, but the TIME_FORMAT is a special case.
You might want to try something like this:

SELECT CAST(TIME_FORMAT(apt.AptDateTime, '%h %i %p') AS CHAR) AS 'Time'

[atd]

That did it, thank you!

[Jorgebon]

I have the same problem but can't get it to work. Here's a query I used to make a list of patients not seen in five years. How would I need to change it to make it work?

SET @pos=0;
SELECT @pos:=@pos+1 as 'Number Of Patients', patient.LName,patient.FName, Date_Format(MAX(procedurelog.ProcDate),'%m/%d/%y') as 'Date Last Visit'
FROM patient,Procedurelog
WHERE procedurelog.PatNum=patient.PatNum
AND patient.BillingType = '40'
AND procedurelog.ProcStatus IN(2,3)
AND patient.PatStatus = '0'
GROUP BY procedurelog.PatNum
HAVING MAX(ProcDate) < '2006-01-01'
ORDER BY patient.Lname, patient.fname

Jorge Bonilla, DMD

[Jorgebon]

OK, I got it now. It works.
Jorge Bonilla, DMD

[B.Thomas]

How can the waiting room report (Query #244) be corrected and updated on the Query example page?

/*Waiting Room Report, shows time each patient arrived, length waiting, was seated,chair time and dismissed time.
also give average waiting time*/
SET @FromDate='2009-01-01' , @ToDate='2009-01-15';
DROP TABLE IF EXISTS tmp1;
CREATE TABLE tmp1 AS
SELECT CONCAT(p.LName,', ',p.FName) AS 'Pat Name', LEFT(a.PatNum,15) AS 'Pat Num', AptDateTime,
LEFT((CASE WHEN DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'/*when there is a real value*/
THEN DATE_FORMAT(DateTimeArrived,'%r')
ELSE '' END),12) AS 'TimeArrived',
LEFT((CASE WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00'/*when there is a real value*/
THEN DATE_FORMAT(DateTimeSeated,'%r')
ELSE '' END),12) AS 'TimeSeated',
LEFT((CASE WHEN DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00'/*when there is a real value*/
THEN DATE_FORMAT(DateTimeDismissed,'%r')
ELSE '' END),12) AS 'TimeDismiss',
LEFT((CASE WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeArrived,'%T')<>'00:00:00'
THEN TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeSeated)-TIME_TO_SEC(DateTimeArrived)),'%H:%i')
ELSE '' END),5) AS 'WaitTime',
LEFT((CASE WHEN DATE_FORMAT(DateTimeSeated,'%T')<>'00:00:00' AND DATE_FORMAT(DateTimeDismissed,'%T')<>'00:00:00'
THEN TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(DateTimeDismissed)-TIME_TO_SEC(DateTimeSeated)),'%H:%i')
ELSE '' END),5) AS 'ChairTime'
FROM Appointment a INNER JOIN patient p ON a.PatNum=p.PatNum
WHERE DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate
ORDER BY a.AptDateTime;
SELECT * FROM tmp1
UNION
SELECT 'Average Wait' AS 'Pat Name', '' AS 'Pat Num','' AS 'AptDateTime', '' AS TimeArrived, '' AS TimeSeated,'' AS TimeDismiss,
FORMAT((SELECT((SUM(TIME_TO_SEC(tmp1.WaitTime))/COUNT(tmp1.WaitTime))/60) FROM tmp1 WHERE tmp1.WaitTime<>''),1) AS WaitTime,
'' AS ChairTime;