TO_CHAR issues in qlikview script

[anuradhaa]

I used below line inside qlikview script SQL under the where clause.

LAST_MOVE_DATETIME BETWEEN  TO_CHAR(SYSDATE-('2'), 'YYYY-MM-DD') AND TO_CHAR(SYSDATE, 'YYYY-MM-DD')

It gives me the error ora-01861 literal does not match format string. But when i run that in database it works

[maxgro]

if LAST_MOVE_DATETIME is a date

you're doing a BETWEEN with a date on the left side and 2 char on the right side

this is the reason of the error

use always date (better) ---> LAST_MOVE_DATETIME BETWEEN SYSDATE-2 AND SYSDATE

or always char

[anuradhaa]

can't have, if i use this i will lost some data since my last move time in in format YYYY-MM-DD hh:mi:ss

[maxgro · Accepted solution]

is it a char or a date?

anyway

oracle sysdate is the current date and time

and if you want the date only I think you can use trunc(sysdate)

the same for LAST_MOVE_DATETIME

if it's a date and time you can use the date and time

or the date only with trunc(LAST_MOVE_DATETIME)

[anuradhaa]

trunc thing works for me. Thank You