strange proble with to_date function in select query

From: Bartek <bpawlows_at_gmail.com>
Date: Fri, 24 Jul 2009 14:45:00 +0200
Message-ID: <ab54e0d40907240545j36695128t140437e6437ed8d7_at_mail.gmail.com>

Hello,

I have a strange problem with to_date function when I use it in select query.

The following query does not give any ORA-nnnnn errors:

select * from (
select tid, sequencenumber, submitdate,
submittime,to_date(submitdate||submittime, 'yyyymmddhh24miss') submitdatetime
from termsheet
where 1=1
and environment = 'Z'
and internalstatuscode = '60ACC'
and submitdate != 'YYYYMMDD'
and submittime != 'HHMMSS'
and submitdate between '20090713' and '20090714'
)

where 1=1
order by sequencenumber

BUT this query:

select * from (
select tid, sequencenumber, submitdate, submittime ,to_date(submitdate||submittime, 'yyyymmddhh24miss') submitdatetime from termsheet
where 1=1
and environment = 'Z'
and internalstatuscode = '60ACC'
and submitdate != 'YYYYMMDD'
and submittime != 'HHMMSS'
and submitdate between '20090713' and '20090714'
)

where 1=1
and submitdatetime >= to_date('20090713135318', 'yyyymmddhh24miss') -- THIS CONDITION ADDED
order by sequencenumber

gives ORA-01841 error:

select tid, sequencenumber, to_date(submitdate||submittime, 'yyyymmddhh24miss') submitdatetime

                                                          *

ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

AND this query:

select * from (
select tid, sequencenumber, submitdate, submittime ,to_date(submitdate||submittime, 'yyyymmddhh24miss') submitdatetime from termsheet
where 1=1
and environment = 'Z'

and internalstatuscode = '60ACC'
and upper(submitdate) != 'YYYYMMDD'    -- HERE THE UPPER IS ADDED
and submittime != 'HHMMSS'

and submitdate between '20090713' and '20090714'
)

where 1=1
and submitdatetime >= to_date('20090713135318', 'yyyymmddhh24miss') order by sequencenumber

gives expected result without any ORA- errors.

Could somebody give me any hint how can I solve this problem?

Thanks in advance,
Bartek

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 24 2009 - 07:45:00 CDT