Dateformat from query datetime or string for specific date format

Ignition
1

Hello!

I got a query than requires a date with specific date format MM/YYYY

SELECT WO.WORKTYPE || ' - ' || WT.WORKTYPENAME AS DESCRIPCION,
       COUNT(WO.WORKORDER) AS PETICIONES
FROM WORKORDER WO
INNER JOIN WORKTYPE WT ON WT.WORKTYPE = WO.WORKTYPE
                      AND WO.COMPANY = WT.COMPANY
WHERE WO.COMPANY = 'GESTIONESP'
  AND TO_CHAR(WO.WORKREQUESTDATE, 'MM/YYYY') = :startDate
  AND WO.BUSINESSUNIT IN (:listSites)
GROUP BY WO.WORKTYPE, WT.WORKTYPENAME
ORDER BY WO.WORKTYPE, WT.WORKTYPENAME

but this format is not valid if the DataType is DateTime....

image
image1156×492 31.1 KB

I guess is ok to just use string as DataType in this case

image
image1159×101 7.37 KB

2

The answer is the same as your related question, Date format for query filter - #4 by Transistor. You should be using datetime objects, not strings.

1 Like
3

Thanks Transistor!, ight now I don't have access to modify the database or control the format of the :startDate, I just transforming Ignition Vision version to Perspective