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?

On 3 Aug 2006 13:12:55 -0700, virgilbvm1 (AT) yahoo (DOT) com wrote:

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

Sybrand Bakker wrote:
Sybrand -

Do you have a special reason why you use
"<= add_month(..)-one_second" instead of "< add_month(..)" ?

best,
Martin

On 4 Aug 2006 17:09:22 -0700, "Martin T."
<bilbothebagginsbab5 (AT) freenet (DOT) de> wrote:

I want to make sure I include the entire day, even if there is a time
in it. This construction also warns me there *is* a time in it.

--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker wrote:
Now you got me confused:
Do we agree that "<= DATE-one_second" == "< DATE" for any DATE value in
Oracle(8/9/10) ?

Select
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') NOW,
to_char(ADD_MONTHS(sysdate, 1)-(1/86400), 'YYYY-MM-DD H24:MI:SS')
M_ADD_MINUS_ONE_SEC,
to_char(ADD_MONTHS(sysdate, 1), 'YYYY-MM-DD HH24:MI:SS') M_ADD,
to_char(to_date('2006-07-01 00:00:00','yyyy-mm-dd
hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') JUL_1ST,
to_char(ADD_MONTHS(to_date('2006-07-01 00:00:00','yyyy-mm-dd
hh24:mi:ss'),1), 'YYYY-MM-DD HH24:MI:SS') AUG_1ST,
to_char(ADD_MONTHS(to_date('2006-07-01 00:00:00','yyyy-mm-dd
hh24:mi:ss')-(1/86400),1), 'YYYY-MM-DD HH24:MI:SS')
AUG_1ST_MINUS_ONE_SEC
from dual;

gets us:

"NOW",
"M_ADD_MINUS_ONE_SEC",
"M_ADD",
"JUL_1ST",
"AUG_1ST",
"AUG_1ST_MUNUS_ONE_SEC"
"2006-08-05 05:05:58",
"2006-09-05 05:05:57",
"2006-09-05 05:05:58",
"2006-07-01 00:00:00",
"2006-08-01 00:00:00",
"2006-07-31 23:59:59"