OraFAQ Forum: SQL & PL/SQL » Date without year

Home » SQL & PL/SQL » SQL & PL/SQL » Date without year (ORACLE, PL/SQL Developer)

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

Date without year [message #476919]

Mon, 27 September 2010 06:23

Sarlula
Messages: 18
Registered: September 2010

Junior Member

Hi,

I got a table with a date-typed column called: "Birth_date",
and I wanna write a function that retrieves all the records
that "got a birthday" this week.

in order to check weather a record got a birthday this week I need to check only the day & month of "Birth_date" that BETWEEN (sysdate-7) AND (sysdate),

but I don't know how..

Someone knows the answer?
Sarlula.

Report message to a moderator

Re: Date without year [message #476923 is a reply to message #476919]

Mon, 27 September 2010 06:34

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use to_char to extract the month and day then use to_date to convert back to a date using a constant year (make sure it's a leap year).
Then all the dates will be in the same year and you can do the comparison on that.

Report message to a moderator

Re: Date without year [message #476924 is a reply to message #476923]

Mon, 27 September 2010 06:34

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

http://www.orafaq.com/forum/t/161676/153040/

Report message to a moderator

Re: Date without year [message #476925 is a reply to message #476919]

Mon, 27 September 2010 06:35

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why not checking if week number is the same one for both dates?
Read Datetime Format Elements, especially WW and IW ones.

Regards
Michel

[Updated on: Mon, 27 September 2010 06:35]

Report message to a moderator

Re: Date without year [message #477283 is a reply to message #476919]

Wed, 29 September 2010 21:54

nestor
Messages: 34
Registered: September 2010

Member

i think you can use this code but i don't know the effect on performance:

SELECT *
FROM table_name
WHERE EXTRACT(DAY FROM birth_date) BETWEEN EXTRACT(DAY FROM SYSDATE-7) AND EXTRACT(DAY FROM SYSDATE)
EXTRACT(MONTH FROM birth_date) BETWEEN EXTRACT(MONTH FROM SYSDATE-7) AND EXTRACT(MONTH FROM SYSDATE)

Report message to a moderator

Re: Date without year [message #477284 is a reply to message #477283]

Wed, 29 September 2010 22:12

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

nestor wrote on Wed, 29 September 2010 22:54

i think you can use this code but i don't know the effect on performance:

Don't worry about performance. Make it work first. Just think what your WHERE clause would be first 6 days of the month, e.g. this friday.

SY.

Report message to a moderator

Re: Date without year [message #477295 is a reply to message #477284]

Thu, 30 September 2010 00:21

nestor
Messages: 34
Registered: September 2010

Member

how about this one?

with test_date as(
SELECT TO_DATE('22-SEP-1980') birthdate from dual union
SELECT TO_DATE('23-SEP-1980') birthdate from dual union
SELECT TO_DATE('24-SEP-1980') birthdate from dual union
SELECT TO_DATE('25-SEP-1980') birthdate from dual union
SELECT TO_DATE('26-SEP-1980') birthdate from dual union
SELECT TO_DATE('27-SEP-1980') birthdate from dual union
SELECT TO_DATE('28-SEP-1980') birthdate from dual union
SELECT TO_DATE('29-SEP-1980') birthdate from dual union
SELECT TO_DATE('30-SEP-1980') birthdate from dual )
SELECT birthdate
FROM TEST_DATE
WHERE to_DATE((lpad(EXTRACT(month FROM birthdate),2,'0')||EXTRACT(DAY FROM birthdate)),'MMDD') BETWEEN trunc(SYSDATE-7) AND trunc(SYSDATE)

BIRTHDATE
-------------------------
23-SEP-80
24-SEP-80
25-SEP-80
26-SEP-80
27-SEP-80
28-SEP-80
29-SEP-80
30-SEP-80
8 rows selected

i just don't know if it should be sysdate-7 or sysdate-6

Report message to a moderator

Re: Date without year [message #477298 is a reply to message #477295]

Thu, 30 September 2010 00:32

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

The following expression is maybe simpler:

SQL> with data as (select sysdate-1000 birthday from dual)
  2  select birthday, to_date(to_char(birthday,'DDMM'),'DDMM') from data;
BIRTHDAY            TO_DATE(TO_CHAR(BIR
------------------- -------------------
04/01/2008 07:33:33 04/01/2010 00:00:00

Regards
Michel

[Updated on: Thu, 30 September 2010 00:33]

Report message to a moderator

Re: Date without year [message #477302 is a reply to message #477298]

Thu, 30 September 2010 00:44

nestor
Messages: 34
Registered: September 2010

Member

This is the simplified version of my previous post. Thanks to michel Smile

Smile

with test_date as(
SELECT TO_DATE('22-SEP-1980') birthdate from dual union
SELECT TO_DATE('23-SEP-1980') birthdate from dual union
SELECT TO_DATE('24-SEP-1980') birthdate from dual union
SELECT TO_DATE('25-SEP-1980') birthdate from dual union
SELECT TO_DATE('26-SEP-1980') birthdate from dual union
SELECT TO_DATE('27-SEP-1980') birthdate from dual union
SELECT TO_DATE('28-SEP-1980') birthdate from dual union
SELECT TO_DATE('29-SEP-1980') birthdate from dual union
SELECT TO_DATE('30-SEP-1980') birthdate from dual )
SELECT birthdate
FROM TEST_DATE
WHERE to_DATE(to_char(birthdate,'MMDD'),'MMDD') BETWEEN SYSDATE-7 AND SYSDATE

Report message to a moderator

Re: Date without year [message #477304 is a reply to message #477302]

Thu, 30 September 2010 00:54

Frank
Messages: 7901
Registered: March 2000

Senior Member

Now all it needs is a trunc around sysdates.

Report message to a moderator

Re: Date without year [message #477306 is a reply to message #477304]

Thu, 30 September 2010 01:03

nestor
Messages: 34
Registered: September 2010

Member

Here it is Frank, as you wish.

with test_date as(
SELECT TO_DATE('22-SEP-1980') birthdate from dual union
SELECT TO_DATE('23-SEP-1980') birthdate from dual union
SELECT TO_DATE('24-SEP-1980') birthdate from dual union
SELECT TO_DATE('25-SEP-1980') birthdate from dual union
SELECT TO_DATE('26-SEP-1980') birthdate from dual union
SELECT TO_DATE('27-SEP-1980') birthdate from dual union
SELECT TO_DATE('28-SEP-1980') birthdate from dual union
SELECT TO_DATE('29-SEP-1980') birthdate from dual union
SELECT TO_DATE('30-SEP-1980') birthdate from dual )
SELECT birthdate
FROM TEST_DATE
WHERE to_DATE(to_char(birthdate,'MMDD'),'MMDD') BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)

Report message to a moderator

Re: Date without year [message #477308 is a reply to message #477306]

Thu, 30 September 2010 01:12

Frank
Messages: 7901
Registered: March 2000

Senior Member

But do you understand why you need the trunc? Hint: you only need it on the first occurrence of sysdate)

Report message to a moderator

Re: Date without year [message #477311 is a reply to message #477308]

Thu, 30 September 2010 01:24

nestor
Messages: 34
Registered: September 2010

Member

yes frank, i belive it is used to remove the fractional parts or elements of sysdate.(hours, mins, sec....)

Report message to a moderator

Re: Date without year [message #477325 is a reply to message #477311]

Thu, 30 September 2010 03:50

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

There's a reason why I said you need to convert the dates to a leap year:

Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> with test_date as(
  2  SELECT TO_DATE('22-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  3  SELECT TO_DATE('23-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  4  SELECT TO_DATE('24-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  5  SELECT TO_DATE('25-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  6  SELECT TO_DATE('26-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  7  SELECT TO_DATE('27-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  8  SELECT TO_DATE('28-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
  9  SELECT TO_DATE('29-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
 10  SELECT TO_DATE('30-SEP-1980', 'DD-MON-YYYY') birthdate from dual UNION
 11  SELECT to_date('29-FEB-1988', 'DD-MON-YYYY') birthdate from dual )
 12  SELECT birthdate
 13  FROM TEST_DATE
 14  WHERE to_DATE(to_char(birthdate,'MMDD'),'MMDD') BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE);
WHERE to_DATE(to_char(birthdate,'MMDD'),'MMDD') BETWEEN TRUNC(SYSDATE)-7 AND TRUNC(SYSDATE)
              *
ERROR at line 14:
ORA-01839: date not valid for month specified


SQL>

Report message to a moderator

Re: Date without year [message #477329 is a reply to message #477325]

Thu, 30 September 2010 04:17

nestor
Messages: 34
Registered: September 2010

Member

Here cookiemonster, please check.

with test_date as(
SELECT TO_DATE('22-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('23-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('24-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('25-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('26-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('27-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('28-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('29-SEP-1980', 'DD-MON-YYYY') birthdate from dual union
SELECT TO_DATE('30-SEP-1980', 'DD-MON-YYYY') birthdate from dual UNION
SELECT to_date('29-FEB-1988', 'DD-MON-YYYY') birthdate from dual )
SELECT birthdate
FROM TEST_DATE
WHERE to_DATE(to_char(birthdate,'MMDD')||'2000','MMDDYYYY') 
BETWEEN to_DATE(to_char(TRUNC(SYSDATE)-7,'MMDD')||'2000','MMDDYYYY') 
AND to_DATE(to_char(SYSDATE,'MMDD')||'2000','MMDDYYYY')

[Updated on: Thu, 30 September 2010 04:20] by Moderator

Report message to a moderator

Re: Date without year [message #477468 is a reply to message #477329]

Thu, 30 September 2010 16:37

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

nestor wrote on Thu, 30 September 2010 05:17

please check.

Still no cigar. Think what will happen if sysdate is within first 7 days of a year.

SY.

[Updated on: Thu, 30 September 2010 16:38]

Report message to a moderator

Re: Date without year [message #477474 is a reply to message #477468]

Thu, 30 September 2010 22:22

nestor
Messages: 34
Registered: September 2010

Member

here SY i've created a new one. Please check

with test_date as(
SELECT TO_DATE('24-SEP-1980', 'DD-MON-YYYY') birthdate, 'N' name FROM dual UNION
SELECT TO_DATE('24-SEP-1980', 'DD-MON-YYYY') birthdate, 'T' name FROM dual UNION
SELECT TO_DATE('25-SEP-1980', 'DD-MON-YYYY') birthdate, 'E' name FROM dual UNION
SELECT TO_DATE('25-SEP-1980', 'DD-MON-YYYY') birthdate, 'U' name FROM dual UNION
SELECT TO_DATE('26-SEP-1980', 'DD-MON-YYYY') birthdate, 'S' name FROM dual UNION
SELECT TO_DATE('26-SEP-1980', 'DD-MON-YYYY') birthdate, 'M' name FROM dual UNION
SELECT TO_DATE('29-FEB-1988', 'DD-MON-YYYY') birthdate, 'J' name FROM dual ),
s_date AS (
SELECT TRUNC(SYSDATE) sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-1 sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-2 sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-3 sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-4 sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-5 sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-6 sys_date FROM dual UNION
SELECT TRUNC(SYSDATE)-7 sys_date FROM dual)
SELECT name
FROM TEST_DATE, S_DATE
WHERE TO_CHAR(birthdate,'MonDD') = TO_CHAR(sys_date,'MonDD')

Report message to a moderator

Re: Date without year [message #477479 is a reply to message #477474]

Thu, 30 September 2010 23:50

Frank
Messages: 7901
Registered: March 2000

Senior Member

All your queries are based on the assumption that they are run on the last day of the week.
Granted, the original question did not define what a week means, but it stated "all the records that "got a birthday" this week."
Hence, you should convert both the DoB and sysdate to a weeknumber (as Michel already suggested very early in the thread)

Report message to a moderator

Re: Date without year [message #477483 is a reply to message #477479]

Fri, 01 October 2010 00:53

nestor
Messages: 34
Registered: September 2010

Member

please give the definition of week for you then so i can try to work it out and may i know what do WW and IW stands for

Report message to a moderator

Re: Date without year [message #477490 is a reply to message #477483]

Fri, 01 October 2010 01:55

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Format Models

Regards
Michel

Report message to a moderator

Re: Date without year [message #477491 is a reply to message #477490]

Fri, 01 October 2010 01:59

nestor
Messages: 34
Registered: September 2010

Member

Thanks michel. I've already searched the net.

Report message to a moderator

Re: Date without year [message #477511 is a reply to message #477474]

Fri, 01 October 2010 05:44

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

nestor wrote on Thu, 30 September 2010 23:22

here SY i've created a new one. Please check

Now you are back to leap year issue. Think what will happen if person is born on February 29. Anyway, check http://www.orafaq.com/forum/mv/msg/161676/476096/148742/#msg_476096

SY.

Report message to a moderator

Re: Date without year [message #477512 is a reply to message #477511]

Fri, 01 October 2010 05:50

nestor
Messages: 34
Registered: September 2010

Member

what do you want SY, are you saying that a person should be included in the list even if it is not a leap year?

Report message to a moderator

Re: Date without year [message #477513 is a reply to message #477512]

Fri, 01 October 2010 05:57

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Obviously.
Are you really going to ignore people born on Feb 29th every 3 out of 4 years?

They'll get upset.

Report message to a moderator

Re: Date without year [message #477515 is a reply to message #477513]

Fri, 01 October 2010 06:10

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Thinking about it that makes the problem even more complicated:

SQL> SELECT to_date('01-MAR-2008', 'DD-MON-YYYY') - 7 week_before_leap,  
  2         to_date('01-MAR-2007', 'DD-MON-YYYY') - 7 week_before_non_leap
  3  FROM dual;

WEEK_BEFO WEEK_BEFO
--------- ---------
23-FEB-08 22-FEB-07

SQL>

So if the date is the 1st Mar for example you always want to include the 22-Feb. But if you convert to a leap year and subtract 7 you'll miss it.

Report message to a moderator

Re: Date without year [message #477518 is a reply to message #477515]

Fri, 01 October 2010 06:49

Frank
Messages: 7901
Registered: March 2000

Senior Member

And that is why one first should define what "a week" means, and then convert both the sysdate and the date of birth to such "a week". Only then one can say if similar issues still arise.

Report message to a moderator

Re: Date without year [message #477519 is a reply to message #477513]

Fri, 01 October 2010 07:12

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

cookiemonster wrote on Fri, 01 October 2010 06:57

Are you really going to ignore people born on Feb 29th every 3 out of 4 years?

They'll get upset.

They will get more upset starting 2097. Then they will be ignored for 7 years Laughing

Laughing

SY.

[Updated on: Fri, 01 October 2010 07:13]

Report message to a moderator

Re: Date without year [message #477522 is a reply to message #477519]

Fri, 01 October 2010 07:19

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Assuming you want last 7 days I suspect the trick involves doing what people born on 29th Feb do every non leap year. Move their bithday - probably to the 28th Feb.

Report message to a moderator

Re: Date without year [message #477526 is a reply to message #477522]

Fri, 01 October 2010 07:41

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

cookiemonster wrote on Fri, 01 October 2010 08:19

Assuming you want last 7 days I suspect the trick involves doing what people born on 29th Feb do every non leap year. Move their bithday - probably to the 28th Feb.

Just to clarify - conditionally move it to 28th (on a non-leap year).

SY.

Report message to a moderator

Re: Date without year [message #477527 is a reply to message #477526]

Fri, 01 October 2010 07:44

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Depends on whether you also want to move sysdate to a constant year (which'd be a non-leap year in a reversal of my original advise).

Report message to a moderator

Re: Date without year [message #477530 is a reply to message #477527]

Fri, 01 October 2010 07:56

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

cookiemonster wrote on Fri, 01 October 2010 08:44

Depends on whether you also want to move sysdate to a constant year (which'd be a non-leap year in a reversal of my original advise).

Then you simply moving "conditional" to SYSDATE side - you will have to deal with MMDD of sysdate - 7 of a leap year (change 0229 to 0228.

SY.

Report message to a moderator

Re: Date without year [message #477531 is a reply to message #477530]

Fri, 01 October 2010 07:59

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

True

Report message to a moderator

Re: Date without year [message #477541 is a reply to message #477531]

Fri, 01 October 2010 11:00

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Roachcoach provided a link to a previous thread that involved the leap year issue and Solomon provided a link to his post in that thread that solved the issue using the Oracle supplied add_months function, which automatically takes leap years into account. The following applies this method to the problem in this thread. Since "got a birthday this week" has not been clearly defined, I have interpreted the original poster's usage of between (sysdate-7) and sysdate to mean that they had a birthdate within the last seven days or today. I have provided an additional test with different date parameters below that, to show that it works for leap years as well.

-- test table and data:

SCOTT@orcl_11gR2> CREATE TABLE a_table AS
  2  SELECT hiredate AS birth_date
  3  FROM   emp
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO a_table VALUES
  2  (TO_DATE ('29-FEB-1988', 'DD-MON-YYYY'))
  3  /

1 row created.

SCOTT@orcl_11gR2> SELECT * FROM a_table
  2  /

BIRTH_DAT
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
29-FEB-88

15 rows selected.

-- query:

SCOTT@orcl_11gR2> SELECT birth_date
  2  FROM   a_table,
  3  	    (SELECT (LEVEL - 1) * 12 offset
  4  	     FROM   DUAL
  5  	     CONNECT BY LEVEL <=
  6  		    (SELECT MONTHS_BETWEEN
  7  			      (SYSDATE,
  8  			       MIN (birth_date))
  9  		     FROM   a_table))
 10  WHERE  ADD_MONTHS (birth_date, offset)
 11  	      BETWEEN (TRUNC (SYSDATE) - 7)
 12  	      AND     SYSDATE
 13  /

BIRTH_DAT
---------
28-SEP-81

1 row selected.

-- test for birthdate on leap year:

SCOTT@orcl_11gR2> SELECT birth_date
  2  FROM   a_table,
  3  	    (SELECT (LEVEL - 1) * 12 offset
  4  	     FROM   DUAL
  5  	     CONNECT BY LEVEL <=
  6  		    (SELECT MONTHS_BETWEEN
  7  			      (SYSDATE,
  8  			       MIN (birth_date))
  9  		     FROM   a_table))
 10  WHERE  ADD_MONTHS (birth_date, offset)
 11  	      BETWEEN TO_DATE ('28-FEB-2010', 'DD-MON-YYYY')
 12  	      AND     TO_DATE ('01-MAR-2010', 'DD-MON-YYYY')
 13  /

BIRTH_DAT
---------
29-FEB-88

1 row selected.

SCOTT@orcl_11gR2>

[Updated on: Fri, 01 October 2010 11:03]

Report message to a moderator

Re: Date without year [message #477543 is a reply to message #477541]

Fri, 01 October 2010 11:18

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The poster originally wanted a function. You could use something like the following to compare dates in any table and return those that had birthdays in the last week or null for those that did not.

SCOTT@orcl_11gR2> CREATE TABLE a_table AS
  2  SELECT hiredate AS birth_date
  3  FROM   emp
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO a_table VALUES
  2  (TO_DATE ('30-SEP-1988', 'DD-MON-YYYY'))
  3  /

1 row created.

SCOTT@orcl_11gR2> SELECT * FROM a_table
  2  /

BIRTH_DAT
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
30-SEP-88

15 rows selected.

SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION had_birthday_last_week
  2    (p_date IN DATE)
  3    RETURN	  DATE
  4  AS
  5    v_date	  DATE;
  6  BEGIN
  7    SELECT p_date
  8    INTO   v_date
  9    FROM   (SELECT (LEVEL - 1) * 12 offset
 10  	       FROM   DUAL
 11  	       CONNECT BY LEVEL <=
 12  		 MONTHS_BETWEEN (SYSDATE, p_date))
 13    WHERE  ADD_MONTHS (p_date, offset)
 14  		BETWEEN (TRUNC (SYSDATE) - 7)
 15  		AND	SYSDATE;
 16    RETURN v_date;
 17  END had_birthday_last_week;
 18  /

Function created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> SELECT birth_date,
  2  	    had_birthday_last_week (birth_date)
  3  FROM   a_table
  4  /

BIRTH_DAT HAD_BIRTH
--------- ---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81 28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
30-SEP-88 30-SEP-88

15 rows selected.

SCOTT@orcl_11gR2> SELECT had_birthday_last_week (birth_date)
  2  FROM   a_table
  3  WHERE  had_birthday_last_week (birth_date) IS NOT NULL
  4  /

HAD_BIRTH
---------
28-SEP-81
30-SEP-88

2 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Date without year [message #477544 is a reply to message #477543]

Fri, 01 October 2010 11:29

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The following is another function, specific to the one table, without passing any parameters.

SCOTT@orcl_11gR2> CREATE TABLE a_table AS
  2  SELECT hiredate AS birth_date
  3  FROM   emp
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO a_table VALUES
  2  (TO_DATE ('30-SEP-1988', 'DD-MON-YYYY'))
  3  /

1 row created.

SCOTT@orcl_11gR2> SELECT * FROM a_table
  2  /

BIRTH_DAT
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
30-SEP-88

15 rows selected.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE date_typ AS OBJECT
  2    (date_col DATE);
  3  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE date_tab AS
  2  TABLE OF date_typ;
  3  /

Type created.

SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION birthdays_last_week
  2    RETURN date_tab PIPELINED
  3  AS
  4  BEGIN
  5    FOR r IN
  6  	 (SELECT birth_date
  7  	  FROM	 a_table,
  8  		 (SELECT (LEVEL - 1) * 12 offset
  9  		  FROM	 DUAL
 10  		  CONNECT BY LEVEL <=
 11  			 (SELECT MONTHS_BETWEEN
 12  				   (SYSDATE,
 13  				    MIN (birth_date))
 14  			  FROM	 a_table))
 15  	  WHERE  ADD_MONTHS (birth_date, offset)
 16  		   BETWEEN (TRUNC (SYSDATE) - 7)
 17  		   AND	   SYSDATE)
 18    LOOP
 19  	 PIPE ROW (date_typ (r.birth_date));
 20    END LOOP;
 21  END birthdays_last_week;
 22  /

Function created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> SELECT * FROM TABLE (birthdays_last_week())
  2  /

DATE_COL
---------
30-SEP-88
28-SEP-81

2 rows selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Date without year [message #478841 is a reply to message #477544]

Tue, 12 October 2010 17:57

bcvellore
Messages: 4
Registered: October 2010

Junior Member

-- To select birth days within the last 7 days (including today)
SELECT birth_date
FROM employees
WHERE MOD (MONTHS_BETWEEN (TRUNC(SYSDATE), birth_date), 12) * 31 BETWEEN 0 AND 7

-- To select birthdays that fall within two days of today (on 12th select days from 10 till 14th)

SELECT birth_date
FROM employees
WHERE MOD (MONTHS_BETWEEN (TRUNC(SYSDATE), birth_date -2), 12) * 31 BETWEEN 0 AND 5

Report message to a moderator

Re: Date without year [message #478843 is a reply to message #478841]

Tue, 12 October 2010 20:03

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

bcvellore,

Not all months have 31 days. Your query produces incorrect results under some circumstances. Please see the tests of your query then mine below. I used a table with a row for every year of a leap year to test against and instead of sysdate I used the first of the month for three different months, one where the preceding month had 28 days, one where the preceding month had 31 days, and one where the preceding month had 30 days. Also note that on February 28th of 2010, people who were born on February 28th and February 29th of 1988 both had birthdays, which is why there should be one extra row in the result set that includes those dates.

test table with full year of birthdays in leap year:

SCOTT@orcl_11gR2> CREATE TABLE employees AS
  2  SELECT TO_DATE ('JAN-01-1988', 'MON-DD-YYYY') + rn birth_date
  3  FROM   DUAL,
  4  	    (SELECT ROWNUM - 1 rn
  5  	     FROM   DUAL
  6  	     CONNECT BY LEVEL <= 366)
  7  /

Table created.

-- your query:

SCOTT@orcl_11gR2> VARIABLE test_date VARCHAR2(11)
SCOTT@orcl_11gR2> EXEC :test_date := 'MAR-01-2010'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT birth_date
  2  FROM   employees
  3  WHERE  MOD
  4  	      (MONTHS_BETWEEN
  5  		(TRUNC (TO_DATE (:test_date, 'MON-DD-YYYY')),
  6  		birth_date), 12) * 31
  7  	      BETWEEN 0 AND 7
  8  /

BIRTH_DAT
---------
25-FEB-88
26-FEB-88
27-FEB-88
28-FEB-88
29-FEB-88
01-MAR-88

6 rows selected.

SCOTT@orcl_11gR2> EXEC :test_date := 'APR-01-2010'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> /

BIRTH_DAT
---------
25-MAR-88
26-MAR-88
27-MAR-88
28-MAR-88
29-MAR-88
30-MAR-88
31-MAR-88
01-APR-88

8 rows selected.

SCOTT@orcl_11gR2> EXEC :test_date := 'MAY-01-2010'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> /

BIRTH_DAT
---------
25-APR-88
26-APR-88
27-APR-88
28-APR-88
29-APR-88
30-APR-88
01-MAY-88

7 rows selected.

-- my query:

SCOTT@orcl_11gR2> EXEC :test_date := 'MAR-01-2010'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT birth_date
  2  FROM   employees,
  3  	    (SELECT (LEVEL - 1) * 12 offset
  4  	     FROM   DUAL
  5  	     CONNECT BY LEVEL <=
  6  		    (SELECT MONTHS_BETWEEN
  7  			      (SYSDATE,
  8  			       MIN (birth_date))
  9  		     FROM   employees))
 10  WHERE  ADD_MONTHS (birth_date, offset)
 11  	      BETWEEN (TRUNC (TO_DATE (:test_date, 'MON-DD-YYYY')) - 7)
 12  	      AND     TRUNC (TO_DATE (:test_date, 'MON-DD-YYYY'))
 13  /

BIRTH_DAT
---------
22-FEB-88
23-FEB-88
24-FEB-88
25-FEB-88
26-FEB-88
27-FEB-88
28-FEB-88
29-FEB-88
01-MAR-88

9 rows selected.

SCOTT@orcl_11gR2> EXEC :test_date := 'APR-01-2010'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> /

BIRTH_DAT
---------
25-MAR-88
26-MAR-88
27-MAR-88
28-MAR-88
29-MAR-88
30-MAR-88
31-MAR-88
01-APR-88

8 rows selected.

SCOTT@orcl_11gR2> EXEC :test_date := 'MAY-01-2010'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> /

BIRTH_DAT
---------
24-APR-88
25-APR-88
26-APR-88
27-APR-88
28-APR-88
29-APR-88
30-APR-88
01-MAY-88

8 rows selected.

SCOTT@orcl_11gR2>

[Updated on: Tue, 12 October 2010 20:06]

Report message to a moderator

Re: Date without year [message #478844 is a reply to message #478841]

Tue, 12 October 2010 20:24

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

bcvellore wrote on Tue, 12 October 2010 18:57

-- To select birth days within the last 7 days (including today)

Really

Laughing

? Assume person was born February 28, 1965 and today is March 5, 2011. There are 5 days between February 28 2011 and March 5, 2011:

SQL> select to_date('3-5-2011','mm-dd-yyyy') - to_date('2-28-2011','mm-dd-yyyy') from dual;

TO_DATE('3-5','MM-DD')-TO_DATE('2-28','MM-DD')
----------------------------------------------
                                             5

SQL>

However:

SQL> with employees as (select date '1965-2-28' birth_date,date '2011-3-5' today from dual)
  2  SELECT MOD (MONTHS_BETWEEN (TRUNC(today), birth_date), 12) * 31
  3    FROM employees
  4  /

MOD(MONTHS_BETWEEN(TRUNC(TODAY),BIRTH_DATE),12)*31
--------------------------------------------------
                                                 8

SQL> with employees as (select date '1965-2-28' birth_date,date '2011-3-5' today from dual)
  2  SELECT birth_date
  3  FROM employees
  4  WHERE MOD (MONTHS_BETWEEN (TRUNC(today), birth_date), 12) * 31 BETWEEN 0 AND 7
  5  /

no rows selected

SQL>

For same reason if person was born February 28, 1965 and today would be March 1, 2011 such person would not show up on list of people born within two days of today:

SQL> with employees as (select date '1965-2-28' birth_date,date '2011-3-1' today from dual)
  2  SELECT birth_date
  3  FROM employees
  4  WHERE MOD (MONTHS_BETWEEN (TRUNC(today), birth_date), 12) * 31 BETWEEN 0 AND 2
  5  /

no rows selected

SQL> with employees as (select date '1965-2-28' birth_date,date '2011-3-1' today from dual)
  2  SELECT MOD (MONTHS_BETWEEN (TRUNC(today), birth_date), 12) * 31
  3  FROM employees
  4  /

MOD(MONTHS_BETWEEN(TRUNC(TODAY),BIRTH_DATE),12)*31
--------------------------------------------------
                                                 4

SQL>

SY.

Report message to a moderator

Re: Date without year [message #478848 is a reply to message #478844]

Tue, 12 October 2010 22:39

bcvellore
Messages: 4
Registered: October 2010

Junior Member

Thanks SY.
By oracle documentation the fractional portion is based on a 31 day month.
Quote:

(download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions089.htm): MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

But, like you pointed out that doesn't seem to be the case. The difference seems to be anywhere from 1 day to 4 days.

SQL> with  employees as (select date '2009-7-31' birth_date,date '2009-8-1' today from dual)
  2  select months_between (today, birth_date) from employees
  3  /

MONTHS_BETWEEN(TODAY,BIRTH_DATE)
--------------------------------
                      .032258065

SQL> with  employees as (select date '2009-6-30' birth_date,date '2009-7-1' today from dual)
  2  select months_between (today, birth_date) from employees
  3  /

MONTHS_BETWEEN(TODAY,BIRTH_DATE)
--------------------------------
                      .064516129

SQL> with  employees as (select date '2009-2-28' birth_date,date '2009-3-1' today from dual)
  2  select months_between (today, birth_date) from employees
  3  /

MONTHS_BETWEEN(TODAY,BIRTH_DATE)
--------------------------------
                      .129032258

Report message to a moderator

Re: Date without year [message #478877 is a reply to message #478848]

Wed, 13 October 2010 02:49

mnitu
Messages: 159
Registered: February 2008
Location: Reims

Senior Member

bcvellore wrote on Wed, 13 October 2010 05:39

Thanks SY.
By oracle documentation the fractional portion is based on a 31 day month.
Quote:
(download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions089.htm): MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

But, like you pointed out that doesn't seem to be the case. The difference seems to be anywhere from 1 day to 4 days.
...

I believe that the documentation is OK. But care must be taken in order to interpret it

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> 
SQL> with  employees as (
  2   select date '2009-7-31' birth_date,date '2009-8-1' today from dual union all
  3   select date '2009-6-30' birth_date,date '2009-7-1' today from dual union all
  4   select date '2009-2-28' birth_date,date '2009-3-1' today from dual
  5   )
  6  select birth_date, today,
  7         months_between (today, birth_date)*31-(31-extract(day from last_day(birth_date)))
  8    from employees
  9  /
 
BIRTH_DATE  TODAY       MONTHS_BETWEEN(TODAY,BIRTH_DAT
----------- ----------- ------------------------------
31/07/2009  01/08/2009                               1
30/06/2009  01/07/2009                               1
28/02/2009  01/03/2009                               1

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	Need example of loop with if stmt that I can copy (2 merged)
Next Topic:	Mail sending in .Dat Format instead of .Html format [merged 3: RL]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 17 16:55:44 CDT 2025