topic Re: TO_CHAR issues in qlikview script in QlikView

TO_CHAR issues in qlikview script

anuradhaa — Mon, 02 Nov 2015 11:58:31 GMT

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 — Mon, 02 Nov 2015 12:09:18 GMT

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 — Mon, 02 Nov 2015 12:24:25 GMT

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 — Mon, 02 Nov 2015 12:34:59 GMT

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 — Wed, 04 Nov 2015 12:37:11 GMT

trunc thing works for me. Thank You