Results 1 to 6 of 6

Thread: Date Format

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Date Format

    We're receiving a text file with a date in the following format:
    Code:
    Mon Sep 25 11:58:06 CDT 2006
    I've found away to convert it to a TIMESTAMP WITH A TIME ZONE
    Code:
    SQL> select to_timestamp_tz('Mon Sep 25 11:58:06 CDT 2006','Dy Mon DD HH:MI:SS TZD YYYY')
      2  from dual;
    
    TO_TIMESTAMP_TZ('MONSEP2511:58:06CDT2006','DYMONDDHH:MI:SSTZDYYYY')
    ---------------------------------------------------------------------------
    25-SEP-06 11.58.06.000000000 AM -08:00
    I'd like to store this in the database as just a DATE datatype. The only way I could find to do so is something like TO_DATE(TO_CHAR(TO_TIMESTAMP_TZ(...))). I couldn't find the correct TO_CHAR format to convert the CDT time into PST in the process (there's the TZH format for the hour, but there's no TZM for the month - for example Jan 2 2007 1am Central is Jan 1 2007 11pm Pacific).

    I tried something that I found like (http://download-east.oracle.com/docs...nctions179.htm)
    Code:
    SQL> select CAST(to_timestamp_tz('Mon Sep 25 11:58:06 CDT 2006','Dy Mon DD HH:MI:SS TZD YYYY') AS TIMESTAMP WITH LOCAL TIME ZONE)
      2  from dual;
    
    CAST(TO_TIMESTAMP_TZ('MONSEP2511:58:06CDT2006','DYMONDDHH:MI:SSTZDYYYY')AST
    ---------------------------------------------------------------------------
    25-SEP-06 11.58.06.000000 AM
    but I still get Central time, not Pacific. The TO_CHAR function has a 3rd parameter which is an 'nlsparam', but I don't know what I'd put here to solve this problem.

    -Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Does this get you closer to what you need?

    http://download-west.oracle.com/docs...htm#sthref2657


    Example The following example converts the datetime value of one time zone to another time zone:

    SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
    'YYYY-MM-DD HH:MIS') AS TIMESTAMP), 'America/New_York')
    AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
    FROM DUAL;

    West Coast Time
    ------------------------------------------------
    01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES

    As an alternative you might need to use NEW_TIME function.
    Last edited by anacedent; 01-31-07 at 17:40.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Definitely closer. So, if the data was submitted with the TZR-expected format ("CST"), then this works:

    Code:
    SQL> select to_timestamp_tz('Mon Sep 25 11:58:06 CST 2006','Dy Mon DD HH:MI:SS TZR YYYY') AT LOCAL "
    NEW_DATE"
      2  from dual;
    
    NEW_DATE
    ---------------------------------------------------------------------------
    25-SEP-06 08.58.06.000000000 AM -08:00
    Since this data uses the TZD-expected format ("CDT"), the time isn't changing:

    Code:
    SQL> select to_timestamp_tz('Mon Sep 25 11:58:06 CDT 2006','Dy Mon DD HH:MI:SS TZD YYYY') AT LOCAL "
    NEW_DATE"
      2  from dual;
    
    NEW_DATE
    ---------------------------------------------------------------------------
    25-SEP-06 11.58.06.000000000 AM -08:00
    I looked into NEW_TIME (definitely a cool suggestion), but that assumes that you're always coming from a particular time zone, and I was hoping to steer clear of such an assumption, if possible.

    -Chuck

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You might have to get creative to write SQL to write SQL that does what you need using NEW_TIME.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Another solution would be something like this (which is certainly less elegant but works ) :
    Code:
    SELECT EntryDate
        -TO_NUMBER(TO_CHAR(EntryDate, 'TZH'))/24
        -TO_NUMBER(TO_CHAR(EntryDate, 'TZM'))/1440
        +TO_NUMBER(SUBSTR(TZ_OFFSET('PST'),1,3))/24
        +TO_NUMBER(SUBSTR(TZ_OFFSET('PST'),5,2))/1440 PST_Date
    FROM 
    (
        SELECT to_timestamp_tz('Thu Feb 01 09:50:06 CST 2007','Dy Mon DD HH:MI:SS TZR YYYY') EntryDate
        FROM DUAL
    );
    Code:
    rbaraer@Ora10g> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
    
    Session altered.
    
    rbaraer@Ora10g> select tz_offset('CST') from dual;
    
    TZ_OFFS
    -------
    -06:00
    
    rbaraer@Ora10g> select tz_offset('PST') from dual;
    
    TZ_OFFS
    -------
    -08:00
    
    rbaraer@Ora10g> SELECT EntryDate
        -TO_NUMBER(TO_CHAR(EntryDate, 'TZH'))/24
        -TO_NUMBER(TO_CHAR(EntryDate, 'TZM'))/1440
        +TO_NUMBER(SUBSTR(TZ_OFFSET('PST'),1,3))/24
        +TO_NUMBER(SUBSTR(TZ_OFFSET('PST'),5,2))/1440 PST_Date
    FROM
    (
        SELECT to_timestamp_tz('Thu Feb 01 09:50:06 CST 2007','Dy Mon DD HH:MI:SS TZR YYYY') EntryDate
        FROM DUAL
    );  2    3    4    5    6    7    8    9   10
    
    PST_DATE
    -------------------
    2007/02/01 07:50:06
    
    rbaraer@Ora10g> SELECT to_timestamp_tz('Thu Feb 01 09:50:06 CST 2007','Dy Mon DD HH:MI:SS TZR YYYY') EntryDate
        FROM DUAL  2  ;
    
    ENTRYDATE
    ---------------------------------------------------------------------------
    01-FEB-07 09.50.06.000000000 AM CST
    
    rbaraer@Ora10g>
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by anacedent
    Does this get you closer to what you need?

    http://download-west.oracle.com/docs...htm#sthref2657


    Example The following example converts the datetime value of one time zone to another time zone:

    SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
    'YYYY-MM-DD HH:MIS') AS TIMESTAMP), 'America/New_York')
    AT TIME ZONE 'America/Los_Angeles' "West Coast Time"
    FROM DUAL;

    West Coast Time
    ------------------------------------------------
    01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES

    As an alternative you might need to use NEW_TIME function.
    You know, as you often say, the CODE tag exists.

    (I could not resist )

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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