Home » SQL & PL/SQL » SQL & PL/SQL » Date Selection (Oracle 10g Windows XP SP2)
Date Selection [message #289044] Wed, 19 December 2007 06:03 Go to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I'm trying to produce a statement that looks at students who have just turned five and when they became five. Eventually this will pan out and look at what month the statement is being run in and adjust the criteria accordingly.

I'm having problems with the date, I am changing the DOB so that I can see when the birthday occured this year using :

to_date(to_char(s.dob, 'ddmm')||to_char(sysdate, 'yyyy'), 'ddmmyyyy')


And I am configuring the date parameters using a similar method :

--Start Date
to_date('0103'||to_char(sysdate, 'yyyy'), 'ddmmyyyy') 

--End Date
to_date('3108'||to_char(sysdate, 'yyyy'), 'ddmmyyyy')


When put together the statement looks so :

select 
s.stud_id, 
to_date(to_char(s.dob, 'ddmm')||to_char(sysdate, 'yyyy'), 'ddmmyyyy')  BDAY_THIS_YEAR, 
to_date('0103'||to_char(sysdate, 'yyyy'), 'ddmmyyyy') ST_DATE, 
to_date('3108'||to_char(sysdate, 'yyyy'), 'ddmmyyyy') END_DATE
from student s 
where trunc(months_between(sysdate, s.dob)/12) = 5 
and 
to_date(to_char(s.dob, 'ddmm')||to_char(sysdate, 'yyyy'), 'ddmmyyyy') 
 >= to_date('0103'||to_char(sysdate, 'yyyy'), 'ddmmyyyy')
and 
to_date(to_char(s.dob, 'ddmm')||to_char(sysdate, 'yyyy'), 'ddmmyyyy') 
 <= to_date('3108'||to_char(sysdate, 'yyyy'), 'ddmmyyyy')
order by 
to_date(to_char(s.dob, 'ddmm')||to_char(sysdate, 'yyyy'), 'ddmmyyyy') 


When I run the statement I get this error :

to_date(to_char(s.dob, 'ddmm')||to_char(sysdate, 'yyyy'), 'ddmmyyyy') >= to_date('0101'||to_char(sy
*
ERROR at line 9:
ORA-01839: date not valid for month specified

I thought by forcing the format to match each date format would work, is there a better method or can anyone spot what I am doing wrong?

[Updated on: Wed, 19 December 2007 06:17] by Moderator

Report message to a moderator

Re: Date Selection [message #289052 is a reply to message #289044] Wed, 19 December 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What is the type of DOB?
2/ if it is date then: "extract(month from dob) between 3 and 8"

Regards
Michel
Re: Date Selection [message #289054 is a reply to message #289044] Wed, 19 December 2007 06:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You probably have a February 29th in your DOB column. When you attach that to year 2007 you get an invalid date.

MHE
Re: Date Selection [message #289055 is a reply to message #289054] Wed, 19 December 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch! Thumbs Up
SQL> select to_date('29/02/2007','DD/MM/YYYY') from dual;
select to_date('29/02/2007','DD/MM/YYYY') from dual
               *
ERROR at line 1:
ORA-01839: date not valid for month specified

Regards
Michel
Re: Date Selection [message #289061 is a reply to message #289055] Wed, 19 December 2007 07:03 Go to previous messageGo to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
The
EXTRACT(MONTH FROM DATE) BETWEEN
statement does the trick, and uses less code, many thanks.

FYI, the statement looks so :

select 
s.stud_id, 
s.dob
from 
student s
where trunc(months_between(sysdate, s.dob)/12) = 5 
and 
extract(month from dob) between 3 and 8
Re: Date Selection [message #289063 is a reply to message #289061] Wed, 19 December 2007 07:22 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: REMOVING LAST 4 CHARACTERS FROM STRING( IMPORTANT )
Next Topic: Using variable to Update columns in a loop
Goto Forum:
  


Current Time: Tue Dec 06 00:20:18 CST 2016

Total time taken to generate the page: 0.19165 seconds