codeninja codeninja - 3 months ago 21
MySQL Question

Cast not working, syntax error

I have the following query:

SELECT t6.USERNAME Nombre, t2.RESOURCENAME Recurso, RESERVATION_DESCRIPTION DescripciĆ³n,
DATE_FORMAT(RESERVATIONDUEDATE, '%M %D %Y de %l:%i %p') + ' a ' +
DATE_FORMAT(RESERVATIONEXPIREDATE, '%;:%i %p') Fecha

FROM `reservation` t1
INNER JOIN `resource` t2 ON t1.RESOURCEID = t2.RESOURCEID
INNER JOIN schedule_intermediate t3 ON t1.SCHEDULEID_GENERATED = t3.SCHEDULEID_GENERATED
INNER JOIN class t4 ON t1.CLASSID = t4.CLASSID
INNER JOIN grade t5 ON t1.GRADEID = t5.GRADEID
INNER JOIN users t6 ON t1.USERID = t6.USERID
WHERE (YEARWEEK(`RESERVATIONDUEDATE`, 1) = YEARWEEK(CURDATE(), 1)) AND (t2.RESOURCELOCATIONID = 3 OR t2.RESOURCELOCATIONID = 4),
CAST(Fecha as char)


Cast is not working however, says it has a syntax error. How can I cast the field named as
Fecha
to a string?

Answer

It is entirely unclear what CAST(Fecha as char) is doing in the WHERE clause. Perhaps you can just remove it:

WHERE (YEARWEEK(`RESERVATIONDUEDATE`, 1) = YEARWEEK(CURDATE(), 1)) AND 
      (t2.RESOURCELOCATIONID IN (3, 4)

But, you also need to change the expression in the SELECT as well. + is addition, not string concatenation:

SELECT t6.USERNAME Nombre, t2.RESOURCENAME Recurso, RESERVATION_DESCRIPTION DescripciĆ³n,
       CONCAT(DATE_FORMAT(RESERVATIONDUEDATE, '%M %D %Y de %l:%i %p'), ' a ',
              DATE_FORMAT(RESERVATIONEXPIREDATE, '%;:%i %p')
             ) as Fecha