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

ORA- Error :


SQL> SELECT TIMESTAMP ,db_user,OS_USER,object_schema,object_name,policy_name,statement_type,sql_text FROM DBA_FGA_AUDIT_TRAIL where policy_name like 'FGA%' and to_date(TIMESTAMP,'dd-mon-yyyy') >= to_date(sysdate-7,'dd-mon-yyyy') ORDER BY TIMESTAMP; 2 3

FROM DBA_FGA_AUDIT_TRAIL where policy_name like 'FGA%' and to_date(TIMESTAMP,'dd-mon-yyyy') >= to_date(sysdate-7,'dd-mon-yyyy')
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string





Cause :

Mismatch in session date format used at session level caused the error ORA-01830.

nls_date_format was set to dd-mon-yyy hh24:mi:ss , however to_date or to_timestamp in query filter where clause set to only date i.e. dd-mon-yyyy without time format hh24:mi:ss

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.


SQL> SELECT TIMESTAMP ,db_user,OS_USER,object_schema,object_name,policy_name,statement_type,sql_text FROM DBA_FGA_AUDIT_TRAIL where policy_name like 'FGA%' and to_timestamp(TIMESTAMP,'dd-mon-yyyy') >= to_timestamp(sysdate-7,'dd-mon-yyyy') ORDER BY TIMESTAMP; 2 3

FROM DBA_FGA_AUDIT_TRAIL where policy_name like 'FGA%' and to_timestamp(TIMESTAMP,'dd-mon-yyyy') >= to_timestamp(sysdate-7,'dd-mon-yyyy')

*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string

Solution :

Either set nls_date_format to only 'dd-mon-yyyy' or accommodate 'hh24:mi:ss' within the to_date/to_timestamp as below to solve error ORA-01830.

SQL> SELECT TIMESTAMP ,db_user,OS_USER,object_schema,object_name,policy_name,statement_type,sql_text
FROM DBA_FGA_AUDIT_TRAIL where policy_name like 'FGA%' and to_timestamp(TIMESTAMP,'dd-mon-yyyy hh24:mi:ss') >= to_timestamp(sysdate-7,'dd-mon-yyyy hh24:mi:ss') ORDER BY TIMESTAMP;