Birth Date Range [message #316035] |
Wed, 23 April 2008 09:33  |
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   |
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 #316049 is a reply to message #316042] |
Wed, 23 April 2008 10:12   |
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 #316064 is a reply to message #316055] |
Wed, 23 April 2008 10:57   |
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  |
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
|
|
|