How to work around ORA-01830: date format picture ends before converting entire input string

When querying a column of type TIMESTAMP WITH TIMEZONE, I wanted to omit the fractional seconds + the time zone information.

The query

 SELECT STATE,
        START_DATE "START DATE"
 FROM   DBA_SCHEDULER_JOBS
 WHERE  OWNER = 'SYS' AND JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS';

would return

STATE      START DATE
---------  --------------------------------
SCHEDULED  02.07.2014 13:351:16,558701 +02:00

and I simply didn't want to display that level of information. When trying to format the string as follows:

TO_DATE(START_DATE,'DD.MM.YYYY HH24:MI:SS') "START DATE"

I ran into the error:

   TO_DATE(START_DATE,'DD.MM.YYYY HH24:MI:SS') "START DATE"
                *
 ERROR at line 2:
 ORA-01830: date format picture ends before converting entire input string

Workaround:
In your session, set

ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD.MM.YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

Then cast the data type from TIMESTAMP WITH TIMEZONE to TIMESTAMP:

CAST(START_DATE AS TIMESTAMP)

and finally, convert to DATE:

TO_DATE(CAST(START_DATE AS TIMESTAMP),'DD.MM.YYYY HH24:MI:SS')

Put together:

SELECT STATE,
        TO_DATE(CAST(START_DATE AS TIMESTAMP),'DD.MM.YYYY HH24:MI:SS') "START DATE"
 FROM   DBA_SCHEDULER_JOBS
 WHERE  OWNER = 'SYS' AND JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS';

Output:

STATE           START DATE
 --------------- ------------------------------
 SCHEDULED       02.07.2014 13:51:16

Another example that worked for me was against a column defined like this:

  DATELASTUPDATED  TIMESTAMP(6)                 NOT NULL,

In my resultset I would only like dates from 29.12.2016, the time of the day is not interesting. Simply use the fuction TRUNC:

WHERE  trunc(DateLastUpdated) = '29.12.2016'