Re: ORA-01861 Literal Does Not Match Format String

On 3 Aug 2006 13:12:55 -0700, virgilbvm1_at_yahoo.com wrote:

>I am using the following code:
>
>Select trunc (To_Date (call_start_dt)),
>count(*)
>from IVRREPORTING.CALLDETAIL
>where
> (call_start_dt > '2006-07-01 00:00:00'
>and
> call_start_dt < '2006-07-31 00:00:00'
>and
> id_calllogging is null)
>group by
> trunc (To_Date (call_start_dt))
>
>Getting the following error:
>
>ORA-01861 Literal Does Not Match Format String
>
>Can anyone offer some advice?

Look at your hard-coded literal.
The default date format is DD-MON-YY
Your literal doesn't conform to the date format. Either you need to change the default date format (not preferred) or you would need to change the literal in to_date('2006-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')

If you want a complete month your second literal is wrong, as you exclude July 31.
You would better use the add_month function like this

<= add_month(to_date('2006-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),1)-(1/3600)
which means August 1, min one second.
In that you also don't have to memorize the number of days in a month, and the formula for a leap year.

--
Sybrand Bakker, Senior Oracle DBA