OraFAQ Forum: SQL & PL/SQL » how to ignore year from date

Home » SQL & PL/SQL » SQL & PL/SQL » how to ignore year from date (Win 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to ignore year from date [message #601540]

Fri, 22 November 2013 00:34

Ali_khan
Messages: 10
Registered: November 2013
Location: Karachi

Junior Member

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 & fetched month & day at a time.

Report message to a moderator

Re: how to ignore year from date [message #601545 is a reply to message #601540]

Fri, 22 November 2013 01:26

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select sysdate, extract(day from sysdate) day, extract(month from sysdate) month,
  2         to_char(sysdate, 'MM-DD') mthdy
  3  from dual;
SYSDATE                    DAY      MONTH MTHDY
------------------- ---------- ---------- -----
22/11/2013 08:25:39         22         11 11-22

Report message to a moderator

Re: how to ignore year from date [message #601549 is a reply to message #601545]

Fri, 22 November 2013 01:40

Ali_khan
Messages: 10
Registered: November 2013
Location: Karachi

Junior Member

hmm, but the issue is that ,I have to use them in many cases for every star, having month + day ,for better readability I wrote only one case,I am new in PL-sql.

Report message to a moderator

Re: how to ignore year from date [message #601550 is a reply to message #601549]

Fri, 22 November 2013 01:48

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Your question looks like a horoscope problem; have a look at this, maybe that's what you are looking for.

SQL> WITH horoscope
  2          AS (SELECT 'aries' what,
  3                     TO_DATE ('21.03', 'dd.mm') date_from,
  4                     TO_DATE ('19.04', 'dd.mm') date_to
  5                FROM DUAL
  6              UNION
  7              SELECT 'taurus' what,
  8                     TO_DATE ('20.04', 'dd.mm') date_from,
  9                     TO_DATE ('20.05', 'dd.mm') date_to
 10                FROM DUAL
 11              UNION
 12              SELECT 'gemini' what,
 13                     TO_DATE ('21.05', 'dd.mm') date_from,
 14                     TO_DATE ('20.06', 'dd.mm') date_to
 15                FROM DUAL
 16              UNION
 17              SELECT 'cancer' what,
 18                     TO_DATE ('21.06', 'dd.mm') date_from,
 19                     TO_DATE ('22.07', 'dd.mm') date_to
 20                FROM DUAL)
 21  SELECT e.ename, hiredate bday, h.what
 22    FROM emp e, horoscope h
 23   WHERE TO_DATE (TO_CHAR (e.hiredate, 'dd.mm'), 'dd.mm') BETWEEN h.date_from
 24                                                              AND h.date_to;

ENAME      BDAY       WHAT
---------- ---------- ------
JONES      02.04.1981 aries
BLAKE      01.05.1981 taurus
CLARK      09.06.1981 gemini

SQL>

P.S. Didn't feel like coding all signs.

[Updated on: Fri, 22 November 2013 01:48]

Report message to a moderator

Previous Topic:	SQL problem
Next Topic:	exact definition of Collection

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:56:04 CDT 2024