RE: ORA-01850: hour must be between 0 and 23

RE: ORA-01850: hour must be between 0 and 23

 

  



Data type of BCDCHDT and BCDCHTM is Numeric

And sample data for BCDCHDT is 1070202
And sample data for BCDCHTM is 125050

Thanks


-----Original Message-----
From: Chedgey Garnet (EXP N-ARM)
[mailto:oracledba-ezmlmshield-x40728604.[Email address protected]
Sent: Tuesday, March 06, 2007 8:07 PM
To: LazyDBA Discussion
Subject: RE: ORA-01850: hour must be between 0 and 23

Krishna,

What is the format of the data in the columns BCDCHDT and BCDCHTM? It is
hard to help without examples but assuming they are both VarChar2
columns of format xYYMMDD and HH24MISS respectively then:

Select...
...
And To_Date( Substr( BCDCHDT, 2 )||BCDCHTM, 'YYMMDDHH24MISS') > dt_chk
...

Should suffice.

Let us know the format of content of the 2 columns and we can help you
further.

Regards

Garnet

-----Original Message-----
From: krishnakumar
[mailto:oracledba-ezmlmshield-x40439390.[Email address protected]
Sent: 06 March 2007 14:19
To: LazyDBA Discussion
Subject: ORA-01850: hour must be between 0 and 23


Hi Folks,

Please help me out ..I tried so many combinations in the AND clause
, but getting the following error .

SQL > Select
BCDCLID,BCDBCOD,BCDSBCD,BCDDESC,BCDAPTO,BCDOCAT,BCDOTYP,BCDPFAM,BCDDTYP,
BCDDCOD
from stg_vw_idirect_EBP001
where length(BCDCHTM) = 6 and length(BCDCHDT) = 7
and to_date(substr(BCDCHDT,2), 'YYMMDD')||'
'||to_char(to_date(substr(BCDCHTM,1),'HH24MISS'),'HH24:MI:SS')> dt_chk
order by BCDCHDT, BCDCHTM;


Error : ORA-01850: hour must be between 0 and 23


Thanks
Krishna


The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s)
and may contain proprietary, confidential or privileged information. If
you are not the intended recipient, you should not disseminate,
distribute or copy this e-mail. Please notify the sender immediately and
destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient
should check this email and any attachments for the presence of viruses.
The company accepts no liability for any damage caused by any virus
transmitted by this email.

www.wipro.com


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com

Oracle LazyDBA home page