problems separating dates by seasons of the year in SQL

I have a TIME dimension in my DW and I want to add a camp to fill in with the season of the year. I want to do something like this:

create or replace
PROCEDURE "PROC_UPDATE_SEASON" 

IS 
CURSOR curs IS   
SELECT * FROM indw.time FOR UPDATE;  
 cur_d NUMBER;   
 cur_m NUMBER;   
 start_d NUMBER;   
 start_m NUMBER;   
 end_d NUMBER;   
 end_m NUMBER;
 cur_Date DATE;     

 BEGIN 
 FOR cs IN curs 
 LOOP 
     cur_d := to_number(to_char(cs.time_DAY,'dd')); 
     cur_m := to_number(to_char(cs.time_DAY,'mm')); 
     cur_date:= cs.time_DAY; -- this is my date dd-mm-yyyy or dd/mm/yyyy


     if ((cur_date in format dd/mm is >= 21/12 and <=31/12) OR cur_date>=01/01 and <=20/03) 
     UPDATE time t set t.time_SEASON = 'Winter' WHERE CURRENT OF curs; 
     else if cur_date_in format dd/mm is between a date and another then it's Spring)
       -- and so on ... 
     END LOOP; 
 END;

Is there a way to achieve this using to开发者_运维百科_char and to_date? I'm trying but always getting errors :( and comparing month and day separately would be a tremendous and, IMO, stupid work.

Could you please give me some tips?

I really just one use something like dd/mm<=another_date_with_only_day_and_month

No need for a cursor and loop:

update indw.time
set t.time_SEASON = case
                      when to_char(time_DAY,'MMDD') between '1221' and '1231' 
                        or to_char(time_DAY,'MMDD') between '0101' and '0320' 
                         then 'Winter'
                      when ...
                    end 

Better still, in Oracle 11G you could add a virtual column to the table based on that CASE expression so that every row automatically had the correct time_season without needing to be updated.

I don't have access to Oracle to play with options for you. But I do have a piece of advice:
- If this code runs often, be cautious of using string conversions and string comparisons, they're usually horribly slow compared to numeric processing
- But, if it's just for occasional use, go with whatever is simplest, and screw performance :)

With that in mind, I'd use EXTRACT... (Note, as I said, I can't test this)

UPDATE indw.time
SET    t.time_SEASON = CASE
                         WHEN EXTRACT(MONTH, time_DAY) < 3 THEN 'Winter'
                         WHEN EXTRACT(MONTH, time_DAY) = 3 THEN
                           CASE WHEN EXTRACT(DAY, time_DAY) <= 20 THEN 'Winter' ELSE 'Spring' END
                         WHEN EXTRACT(MONTH, time_DAY) < 6 THEN 'Spring'
                         WHEN EXTRACT(MONTH, time_DAY) = 6 THEN
                           CASE WHEN EXTRACT(DAY, time_DAY) <= 20 THEN 'Spring' ELSE 'Summer' END
                         WHEN EXTRACT(MONTH, time_DAY) < 9 THEN 'Summer'
                         WHEN EXTRACT(MONTH, time_DAY) = 9 THEN
                           CASE WHEN EXTRACT(DAY, time_DAY) <= 20 THEN 'Summer' ELSE 'Autumn' END
                         WHEN EXTRACT(MONTH, time_DAY) < 12 THEN 'Autumn'
                         WHEN EXTRACT(MONTH, time_DAY) = 12 THEN
                           CASE WHEN EXTRACT(DAY, time_DAY) <= 20 THEN 'Autumn' ELSE 'Winter' END
                       END

I would do it this way to get the season from a date:

CASE
    WHEN EXTRACT(MONTH FROM time_DAY) IN (12,1,2) THEN 'winter'
    WHEN EXTRACT(MONTH FROM time_DAY) IN (3,4,5) THEN 'spring'
    WHEN EXTRACT(MONTH FROM time_DAY) IN (6,7,8) THEN 'summer'
    WHEN EXTRACT(MONTH FROM time_DAY) IN (9,10,11) THEN 'fall'
END season