Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to solve this query
Please help me to solve this query [message #376350] Wed, 17 December 2008 01:38 Go to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
HI,

Can you please help me in the following query.

select * from per_all_people_f where to_char(DATE_OF_BIRTH,'DD-MON') > to_char(sysdate,'DD-MON')
and to_char(DATE_OF_BIRTH,'DD-MON') < to_char(sysdate+21,'DD-MON').

where DATE_OF_BIRTH is the date column.

I need query the data from '12/17' to '01/07' (mm/dd).

Thanks & Regards,
Abdul Huda
Re: Please help me to solve this query [message #376356 is a reply to message #376350] Wed, 17 December 2008 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
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 (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Read TO_DATE function and related links.

Regards
Michel
Re: Please help me to solve this query [message #376403 is a reply to message #376356] Wed, 17 December 2008 04:05 Go to previous messageGo to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
I tried but not able to get proper data.
For e.g please consider the following data.

EName Birth_date
===== ==========
Harry 10-JAN-2001
Jim 21-AUG-1999
Tom 16-JUL-1978
Jerry 11-DEC-2002

I need to retrive data who are born in between 09-DEC and 30-JAN.
As from the above data 'Harry and Jerry' should be retrieved.
Re: Please help me to solve this query [message #376409 is a reply to message #376403] Wed, 17 December 2008 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post what you tried and post it formatted.

Regards
Michel
Re: Please help me to solve this query [message #376414 is a reply to message #376403] Wed, 17 December 2008 04:33 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your mistake is to convert the dates to strings in the format that you do.

As a string, 10-APR-2001 comes BEFURE 10-JAN-2001,

SELECT * 
FROM   per_all_people_f 
WHERE  (to_char(date_of_birth,'MON') = 'DEC' AND to_number(to_char(date_of_birth,'dd')) >= 9)
OR (to_char(date_of_birth,'MON') = 'JAN' AND to_number(to_char(date_of_birth,'dd')) <= 30)
Previous Topic: Dynamic queryin based on number of inputs provided
Next Topic: checking for blank line
Goto Forum:
  


Current Time: Mon Feb 17 15:03:23 CST 2025