Date Issue

[r_adusumil]

Hi All,

I am trying to update a date column ina table with following query and i get this error
"ORA-01841:(full) year must be between -4713 and +9999, and not be 0."
please help,

the query that i am executing is

update temp_ts
set st_time = decode(start_time,null, to_date(trunc(rk_date) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am'),to_date(trunc(st_time) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am')),
it_time = decode(it_time,null,to_date(trunc(rk_date) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am'),to_date(trunc(it_time) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am'))
where hours = 0 and ak_time = 0

any help is appreciated,

Thank You

[jmodic]

to_date(trunc(rk_date) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am')

This ecpression (and all the simmilar you are using) is wrong because you are forcing oracle to use implicit conversion from date to char, which results in unpredictable results and errors in your case. The problem is the following piece:

trunc(rk_date) || ' 8:00:00 AM'

the first part of the expression results in DATE datatype and if you perform a concatenation on it it must be first converted to CHAR type. So what you realy need is:

to_date(TO_CHAR(rk_date,'dd-mon-yyyy') ||
        ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am')

or even better, more simple and more readable:

TRUNC(rk_date) + 8/24

[r_adusumil]

hi jmodic thanks for the help,
i tried to execute the query by modifying it,
i removed trunc an di used to_char but it still give sme some errors..
can u please modify the query that originally posted and paste it.

Thanks for the help

[chrisrlong]

Originally posted by r_adusumil
can u please modify the query that originally posted and paste it.

Doubtful. He already gave you the solution. The owness is now on you to implement it correctly. If your new statement gives an error, then post your new statement so we can see what went wrong.

- Chris

[stecal]

onus

[r_adusumil]

this is the modified one as per suggested and now i get ora-00927 missing equal sign

update temp_ts
set start_time = decode(start_time,null, to_date(to_char(rk_date,'dd-mon-yyyy')) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am'),to_date(to_char(st_time,'dd-mon-yyyy')) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am')),
it_time = decode(it_time,null,to_date(to_char(rk_date,'dd-mon-yyyy')) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am'),
to_date(to_char(it_time,'dd-mon-yyyy')) || ' 8:00:00 AM','dd-mon-yyyy hh:mi:ss am'))
where hours = 0 and break_time = 0

[chrisrlong]

Originally posted by stecal
onus

Thank you - I knew in my heart I had it wrong, but I was too bloody lazy to go look it up.

(hangs head in shame)

- Chris

[r_adusumil]

Thanks for the message JMODIC
Thanks Every one,
it worked...the equal to sign error occured coz of some missing paranthesis,
i was expecting it was some other weird errors but its not..
thank you..