Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44

    Unanswered: Date Format Conversion with TIMEZONE

    Hi All,

    I have a data in varchar2(30) field which stores date with 'September 06, 2004 10:59:22 MEST' format with TIME ZONE 'MEST'.
    Can anyone please tell me how to convert this date to 'DD-MON-YYYY HH24:MIS MEST' format. What is the keyword to handle TIME ZONE detail.

    Thanks in Advance
    Thanks and Regards,
    Mahesh

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    this is an age-old problem.
    attempting to convert a timezone type of date format.

    it's easy if you don't have the timezone ...
    WITH the timezone, how do you want to display the date/time?
    do you want the time according to the host or according to the client?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why did you store the time as a literal instead as a timezone column? That makes NO sense. After saying that, the following will convert to a timezone

    select to_timestamp_tz(my_date_string,'Month dd, YYYY hh24:mi:ss tzh') from dual;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Bill,

    Did you test that?
    TZH is for TimeZone Hour.
    Unfortunately, the guy is still screwed.

    PHP Code:
    > list
      
    1  select
      2  to_timestamp_tz
    ('September 06, 2004 10:59:22 MEST','Month dd, YYYY hh24:mi:ss tzh')
      
    3from dual
    > /
    to_timestamp_tz('September 06, 2004 10:59:22 MEST','Month dd, YYYY hh24:mi:ss tzh')
                    *
    ERROR at line 2:
    ORA-01858a non-numeric character was found where a numeric was expected 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Duck,
    You are right. I messed up. I should have used tzr (time zone region), not tzh. Unfortunally, I don't have an Oracle 9i machine handy so I wasn't able to test it.

    select to_timestamp_tz(my_date_string,'Month dd, YYYY hh24:mi:ss tzr') from dual;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    yes, I got that far as well.
    the problem for this guy is that he is not using REGIONS, he is using the timezone ABBREVIATION (unfortunately for him/her).
    PHP Code:
    /* ABBREVIATION does not compute "MEST" */
      
    1  select
      2  to_timestamp_tz
    ('September 06, 2004 10:59:22 MEST','Month dd, YYYY hh24:mi:ss tzr')
      
    3from dual
    > /
    to_timestamp_tz('September 06, 2004 10:59:22 MEST','Month dd, YYYY hh24:mi:ss tzr')
                    *
    ERROR at line 2:
    ORA-01882timezone region  not found


    edit
    Wrote file afiedt
    .buf
    /* now I change the ABBREV to the actual REGION "MET" */
      
    1  select
      2  to_timestamp_tz
    ('September 06, 2004 10:59:22 MET','Month dd, YYYY hh24:mi:ss tzr')
      
    3from dual
    > /

    TO_TIMESTAMP_TZ('SEPTEMBER06,200410:59:22MET','MONTHDD,YYYYHH24:MI:SSTZR')
    ---------------------------------------------------------------------------
    06-SEP-04 10.59.22.000000000 AM MET

      1  select 
    from v$timezone_names
      2
    where tzabbrev 'MEST'
    > /

    TZNAME     TZABBREV
    ---------- ------------------------
    MET        MEST 
    Last edited by The_Duck; 09-08-04 at 12:47.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    unless he writes a procedure to modify his strings to use the zone code instead of the abbrev, he is out of luck. Maybe next time he will use a timestamp instead of varchar2
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    44
    Dear All,

    Thanks for the valuable suggestions.
    Next time i will make sure that i will make sure i will use timestamp instead of varchar2.
    Thanks and Regards,
    Mahesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •