Home » SQL & PL/SQL » SQL & PL/SQL » Birth Date Range (Orcale, Banner)
Birth Date Range [message #316035] Wed, 23 April 2008 09:33 Go to next message
rterry
Messages: 6
Registered: June 2007
Junior Member
The admission office want to be able to send birthday card to student who will be having a birthday in the next two weeks to applied students. Since Banner stores month as APR, MAY...
It is giving me fits to try and set a range. It works perfectly within the same month but if you say
begin date 22-APR and end date 07-May it returns nothing and I understand why. Because 7 is less than 22. I want to set this up as a parameter so the user can enter the dates themselves. Some how the APR has to converted to 4 and May to 5. Am I off track. Anyone know of this problem and how I can get around it.
Any help will be greatly appreacitaed. One other thing. I am trying to use ODS to created this report

Randy Terry
Jacksonville State University
Jacksonville, Alabama

[Updated on: Wed, 23 April 2008 09:35]

Report message to a moderator

Re: Birth Date Range [message #316037 is a reply to message #316035] Wed, 23 April 2008 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is why you store dates in DATE columns, not VARCHAR2 columns.

If you've got a string in the format 'dd-MON' (eg 22-APR), you can convert it to a date in the current year by
to_date('22-APR','dd-MON')

This will give you a variable you can do proper range comparisons on.
Re: Birth Date Range [message #316042 is a reply to message #316037] Wed, 23 April 2008 09:58 Go to previous messageGo to next message
rterry
Messages: 6
Registered: June 2007
Junior Member
Sorry, still not understanding. I am looking for 22-APR to become 22-04. Is that possible?
Re: Birth Date Range [message #316049 is a reply to message #316042] Wed, 23 April 2008 10:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Once you've got the field as a DATE, you can use TO_CHAR to convert it into any format you like.

I suspect that you'd be better off comparing the variables as dates, rather than as strings
Re: Birth Date Range [message #316055 is a reply to message #316035] Wed, 23 April 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(to_date('13-NOV.','DD-MON')-15,'DD/MM') alarm from dual;

ALARM
----- 
29/10

Regards
Michel
Re: Birth Date Range [message #316064 is a reply to message #316055] Wed, 23 April 2008 10:57 Go to previous messageGo to next message
rterry
Messages: 6
Registered: June 2007
Junior Member
Thank you. This has been very helpfull. If I can not get this to work in ODS, I will write a psql query that will be ran nightly.

Thank you,
Randy
Re: Birth Date Range [message #316095 is a reply to message #316064] Wed, 23 April 2008 14:15 Go to previous message
rterry
Messages: 6
Registered: June 2007
Junior Member
One last thing. I got it to work through ODS. I converted the alpha month to a numberic and combined it with the day. Now all the user has to do is enter 0501(MMDD) and 0515 (MMDD).

Again. You were a great help. Tell the boss you are taking the rest of the off. haha.

Thanks,

Randy
Previous Topic: Materialized view -- insufficient privileges
Next Topic: sql to group by schema
Goto Forum:
  


Current Time: Sun Dec 11 06:18:59 CST 2016

Total time taken to generate the page: 0.07682 seconds