-
Date Issue
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
-
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:
Code:
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:
Code:
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:
Code:
TRUNC(rk_date) + 8/24
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Date Issue
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
-
Re: Date Issue
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
-
-
Date Issue
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
-
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
-
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..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|