Date Issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Date Issue

  1. #1
    Join Date
    Aug 2001
    Posts
    40

    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Aug 2001
    Posts
    40

    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    onus

  6. #6
    Join Date
    Aug 2001
    Posts
    40

    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

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Aug 2001
    Posts
    40
    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
  •  


Click Here to Expand Forum to Full Width