Date format for query filter

Ignition
1

Hello!

A query:

SELECT WO.REQUESTER AS SOLICITANTE,
       COUNT(WO.WORKORDER) AS "NUMERO PETICIONES"
FROM WORKORDER WO
WHERE WO.COMPANY = 'GESTIONESP'
  AND TO_CHAR(WO.WORKREQUESTDATE, 'MM/YYYY') = :startDate
  AND WO.BUSINESSUNIT IN (:listSites)
GROUP BY WO.REQUESTER
ORDER BY WO.REQUESTER;

is expecting a date with format MM/YYYY to give me the data.

I would like to know how can I change the format of the datetime picker
image

image

to fill the query.

2

You can bind to formattedDate of datetime picker and there is date format under I guess props, write MM-YYYY in it.

1 Like
3

Yes, thanks Gaurav, that´s the point, I binded to DateTimeInput.props.value

and added the Format Transform like this:

image
image711×684 26.1 KB

4

Why? Why are you not using proper timestamps in your query?

This means that every row in your data table must be converted to see if it matches the startDate. You can't take advantage of the database index on the WORKREQUESTDATE column (if you have created one - which you should if that is the way you will query). Your query will be slow.

1 Like
5

Try this and pass it the raw value from the DateTimeInput..

SELECT WO.REQUESTER AS SOLICITANTE,
       COUNT(WO.WORKORDER) AS "NUMERO PETICIONES"
FROM WORKORDER WO
WHERE WO.COMPANY = 'GESTIONESP'
  AND WO.WORKREQUESTDATE 
        BETWEEN :startDate AND DATEADD(month, 1, :startDate)
  AND WO.BUSINESSUNIT IN (:listSites)
GROUP BY WO.REQUESTER
ORDER BY WO.REQUESTER;

The BETWEEN clause can now use the database index (if there is one).

1 Like
6

DATEADD is not recognize by Oracle so I used ADD_MONTHS instead

SELECT WO.REQUESTER AS SOLICITANTE,
       COUNT(WO.WORKORDER) AS "NUMERO PETICIONES"
FROM WORKORDER WO
WHERE WO.COMPANY = 'GESTIONESP'
  AND WO.WORKREQUESTDATE 
        BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD') 
            AND ADD_MONTHS(TO_DATE(:startDate, 'YYYY-MM-DD'), 1)
  AND WO.BUSINESSUNIT IN (:listSites)
GROUP BY WO.REQUESTER
ORDER BY WO.REQUESTER;

but got the error

[ORA-01830: date format picture ends before converting entire input string]

(oracle - ORA-01830: date format picture ends before converting entire input string / Select sum where date query - Stack Overflow)

image
image1283×139 8.53 KB

7

Can you measure any improvement in query time?

8

Can´t yet as I got the eror ORA-01830 but I´m sure using DateTime format is the most time effective way. I´m just using older queries I didn´t do for the Vision-Perspective conversion