RE: strange proble with to_date function in select query

From: Mercadante, Thomas F (LABOR) <"Mercadante,>
Date: Fri, 24 Jul 2009 09:10:46 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF023CA9FF_at_EXCNYSM0A1AJ.nysemail.nyenet>

Bartek,

It seems that the submitdate column may have lower or mixed case 'yyyymmdd'??? Have you checked this?

Select distinct submitdate
Where upper(submitdate) = 'YYYYMMDD'

??

Tom

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bartek Sent: Friday, July 24, 2009 8:45 AM
To: oracle-l
Subject: strange proble with to_date function in select query

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




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

Received on Fri Jul 24 2009 - 08:10:46 CDT