RE: strange proble with to_date function in select query
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <bpawlows@xxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Jul 2009 09:10:46 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
