Re: ORA-01861 Literal Does Not Match Format String

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 5 Aug 2006 03:09:51 -0700
Message-ID: <1154772591.706506.133060@75g2000cwc.googlegroups.com>

Sybrand Bakker wrote:
> On 4 Aug 2006 17:09:22 -0700, "Martin T."
> <bilbothebagginsbab5_at_freenet.de> wrote:
>
> >Sybrand Bakker wrote:
> >> On 3 Aug 2006 13:12:55 -0700, virgilbvm1_at_yahoo.com wrote:
> >>
> >> 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.
> >
> >Sybrand -
> >
> >Do you have a special reason why you use
> >"<= add_month(..)-one_second" instead of "< add_month(..)" ?
> >
>
> 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.
>

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"

Received on Sat Aug 05 2006 - 05:09:51 CDT