Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Query based on values in another table

    OK - here we go, I am trying to find specific values that fall between daylight saving dates. We have a table that stores the daylight saving dates that the changes happen on.

    I would like to return the daylight saving value based on the current date.
    As there is an in and an out date, we have to disregard the value that is not appropriate, as every year has two entries I need to be able to get the correct values based on the current date.

    Day light saving in 4th month to the 8th month
    Day light saving out 9th month to the 3rd month in the next year

    I am querying an Oracle 10g database.

    This is one attempt I have tried, this get the singe value but does not take into account the change out of daylight saving date, so would fail if the current date is greater than the 8th month.

    Code:
    select tz_a.abbr                      
          from timezone_offset tzo
          join (select min(tz_o.effective_date) min_dt
                          ,tz_o.offset_abbreviation abbr
                    from timezone_offset tz_o
                  where to_char(to_date(effective_date, 'yyyymmdd'),'yyyy') = to_char(sysdate, 'yyyy')
                      and (to_number(substr(effective_date,5,2)) >= 4 and to_number(substr(effective_date,5,2))  <= 8)
                  group by tz_o.effective_date
                               ,tz_o.offset_abbreviation) tz_a on (tzo.effective_date = tz_a.min_dt)
          where to_char(to_date(effective_date, 'yyyymmdd'),'yyyy') = to_char(sysdate, 'yyyy')

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    complete the SQL statement below

    CREATE TABLE timezone_offset
    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
    Apr 2012
    Posts
    2
    I hope this is the data you are asking for?

    Column Name ID Pk Null? Data Type Default Histogram Encryption Alg Salt

    TIMEZONE_OFFSET_ID 1 1 N NUMBER (9) 0 Height Balanced
    TIMEZONE_ID 2 N NUMBER (9) 0 Frequency
    EFFECTIVE_DATE_TIME 3 N DATE sysdate None
    EFFECTIVE_DATE 4 N CHAR (8 Byte) '00000000' Height Balanced
    EFFECTIVE_TIME 5 N NUMBER (9) 0 None
    OFFSET_FROM_UTC 6 N NUMBER (9) 0 None
    LAST_UPDATE_DATE_TIME 7 N DATE sysdate None
    LAST_UPDATE_USER 8 N VARCHAR2 (10 Char) ' ' None
    OFFSET_PLUS_MINUS 9 N NUMBER (1) 0 None
    OFFSET_ABBREVIATION 10 N VARCHAR2 (6 Char) ' ' None
    GLXTIMESTAMP 11 Y NUMBER (15) None

Posting Permissions

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