1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to ignore year from date and get month ,day

Discussion in 'SQL PL/SQL' started by Ali_khan, Nov 22, 2013.

  1. Ali_khan

    Ali_khan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    DECLARE
    V_HD EMP.HIREDATE%TYPE;
    V_FM VARCHAR2(30);
    V_STAR VARCHAR2(30);
    BEGIN
    V_FM :='&FORMAT_MASK';
    V_HD:=TO_DATE('&HIREDATE',V_FM);
    DBMS_OUTPUT.PUT_LINE(V_HD||CHR(10)||TO_CHAR(V_HD,V_FM));
    V_STAR:= CASE WHEN V_HD BETWEEN TO_CHAR('01-01-%%%%', 'dd-mm') AND TO_CHAR('01-02-%%%%', 'dd-mm')
    THEN 'YOUR STAR is capricorn'
    ELSE 'any other star'
    END;
    DBMS_OUTPUT.PUT_LINE(V_STAR);

    END



    I want to avoid year from date , & get the month & day , so there star will define,,
    how to ignore year ?
    suppose there are many cases for every star in year?
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Ali,

    Try like below.

    Code (SQL):
    DECLARE
    V_HD EMP.HIREDATE%TYPE;
    V_FM VARCHAR2(30);
    V_STAR VARCHAR2(30);
    BEGIN
    V_FM :='&FORMAT_MASK';
    V_HD:=TO_DATE('&HIREDATE',V_FM);
    DBMS_OUTPUT.PUT_LINE(V_HD||CHR(10)||TO_CHAR(V_HD,V_FM));
    V_STAR:= CASE WHEN V_HD BETWEEN TO_DATE('01-01'||'-'||to_char(V_HD,'yyyy'),'dd-mm-yyyy') AND TO_DATE('01-02'||'-'||to_char(V_HD,'yyyy'),'dd-mm-yyyy')
    THEN 'YOUR STAR is capricorn'
    ELSE 'any other star'
    END;
    DBMS_OUTPUT.PUT_LINE(V_STAR);
    END;

    Regards
    Sambasiva Reddy.K
     
    Ali_khan likes this.
  3. Ali_khan

    Ali_khan Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    This is exactly, I was looking for. thank you