how to ignore year from date

Question

0.00/5 (No votes)

See more:

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_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 ?

Posted 21-Nov-13 10:42am

Ali_100

Add a Solution

Comments

ZurdoDev 21-Nov-13 16:59pm

I don't know about Oracle but Microsoft SQL has a Month() and Day() function. I imagine Oracle does too.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ali_100 · Answer 1 · 2013-11-22T06:29:00

I did it by

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;

deepakdynamite · Answer 2 · 2013-11-22T01:49:00

Solution 3

select REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') +' ' + CONVERT(VARCHAR(12), GETDATE(), 114)

Posted 22-Nov-13 1:49am

deepakdynamite

Comments

Maciej Los 22-Nov-13 12:38pm

Oracle, not MS SQL...

Ali_100 · Answer 3 · 2013-11-22T11:21:00

DECLARE
V_HD EMP.HIREDATE%TYPE;
V_FM VARCHAR2(30);
V_STAR VARCHAR2(30);
V_MONTH VARCHAR2(12);
V_DD NUMBER(3);
BEGIN
V_FM :='&FORMAT_MASK';
V_HD:=TO_DATE('&HIREDATE',V_FM);
V_MONTH:= TO_CHAR(V_HD,'fmmonth');
V_DD:= to_number(to_char(V_HD,'fmDD'));
V_STAR:= CASE WHEN V_MONTH='january' AND V_DD <= 20 THEN

'Your star is Capricorn'
WHEN V_MONTH='january' AND V_DD > 20 THEN
'Your star is Aquarius'

WHEN V_MONTH='febraury' AND V_DD <= 20 THEN
'Your star is Aquarius'
WHEN V_MONTH='febraury' AND V_DD > 20 THEN
'Your star is Pisces'

WHEN V_MONTH='march' AND V_DD <= 20 THEN
'Your star is Pisces'
WHEN V_MONTH='march' AND V_DD > 20 THEN
'Your star is Aries'

WHEN V_MONTH='april' AND V_DD <= 20 THEN
'Your star is Aries'
WHEN V_MONTH='april' AND V_DD > 20 THEN
'Your star is Taurus'

WHEN V_MONTH='may' AND V_DD <= 20 THEN
'Your star is Taurus'
WHEN V_MONTH='may' AND V_DD > 20 THEN
'Your star is Gemini'

WHEN V_MONTH='june' AND V_DD <= 20 THEN
'Your star is Gemini'
WHEN V_MONTH='may' AND V_DD > 20 THEN
'Your star is Cancer'

WHEN V_MONTH='july' AND V_DD <= 20 THEN
'Your star is Cancer'
WHEN V_MONTH='july' AND V_DD > 20 THEN
'Your star is Leo'

WHEN V_MONTH='august' AND V_DD <= 20 THEN
'Your star is Leo'
WHEN V_MONTH='august' AND V_DD > 20 THEN
'Your star is Virgo'

WHEN V_MONTH='september' AND V_DD <= 20 THEN
'Your star is Virgo'
WHEN V_MONTH='september' AND V_DD > 20 THEN
'Your star is Libra'

WHEN V_MONTH='october' AND V_DD <= 20 THEN
'Your star is Libra'
WHEN V_MONTH='october' AND V_DD > 20 THEN
'Your star is scorpio'

WHEN V_MONTH='november' AND V_DD <= 20 THEN
'Your star is scorpio'
WHEN V_MONTH='november' AND V_DD > 20 THEN
'Your star is sagitarius'

WHEN V_MONTH='december' AND V_DD <= 20 THEN
'Your star is sagitarius'
WHEN V_MONTH='december' AND V_DD > 20 THEN
'Your star is Capricorn'

END;

DBMS_OUTPUT.PUT_LINE(V_STAR);
DBMS_OUTPUT.PUT_LINE(V_MONTH);
DBMS_OUTPUT.PUT_LINE(V_DD);
END;

how to ignore year from date

3 solutions

Solution 4

Solution 3

Solution 5

Add your solution here

Preview 0