I have a date of birth (Date) field in my database.

I want an SQL statement to show all records where the birthday is falling within the next
30 days.

I have searched everywhere and cant find a solution that will wok in DBISAM

Any help would be very appreciated - thank you

Dave

WHERE datefield >= current_date and datefield <= current_date +30

Roy Lambert [Team Elevate]

Hi Roy, but the date field in the database may appear like 01/02/1980
as a date of birth?

It is not in the same year as the CURRENT_DATE

So the date field may show 01/02/1980 as a birthay
I want to search all records where this birthday is due in the next 30 days from today.

A simple date search fails on the year.

Thanks


Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dave

WHERE datefield >= current_date and datefield <= current_date +30

Roy Lambert [Team Elevate]

Dave

Woops forgot that. What you need to do is bring the birthday into the current year and then testing is easy

try

WHERE CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE) BETWEEN CURRENT_DATE AND CURRENT_DATE + 30



Roy Lambert

Absolutely fantastic. Thank you Roy

Quick question. Will this still work if the next birthday is in the next year, not the
current year?

Thanks again



Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dave

Woops forgot that. What you need to do is bring the birthday into the current year and
then testing is easy

try

WHERE CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE) BETWEEN CURRENT_DATE AND CURRENT_DATE + 30



Roy Lambert

Dave


Don't thank just me - thank the people who's example I nicked to start with

It should be fine. All that will happen is that CURRENT_DATE + 30 will be in the next year. I don't know exactly how DBISAM's SQL does the comparison but if its like Delphi the date will be transformed into an offset from a base date and the resulting offsets compared.

Roy Lambert [Team Elevate]

I have seen some very complicated SQL to do this and this is the most efficient I have seen.
I will put it in my little book of code snipetts

Thanks again


Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dave


Don't thank just me - thank the people who's example I nicked to start with

It should be fine. All that will happen is that CURRENT_DATE + 30 will be in the next
year. I don't know exactly how DBISAM's SQL does the comparison but if its like Delphi the
date will be transformed into an offset from a base date and the resulting offsets compared.

Roy Lambert [Team Elevate]

"Dave B" <david@itfx.com.au> wrote in message
news:9A291E9E-CF3B-4039-815C-24C6D03E76B0@news.elevatesoft.com...
>I have seen some very complicated SQL to do this and this is the most
>efficient I have seen.
> I will put it in my little book of code snipetts
>

Just make sure "next 30 days" is *really* what you want. For example, what
is "next 30 days" from Feb 5th? March 7th. If you run this program on the
5th every month, somebody whose birthday is March 6 gets two presents
one during the Feb run, another during the March run.

Anyway, if what you really want is "same day next month", you'll have to do
a little more processing with IF  statements, but the general concept is the
same.

Robert

> Thanks again
>
>
> Roy Lambert <roy.lambert@skynet.co.uk> wrote:
>
> Dave
>
>
> Don't thank just me - thank the people who's example I nicked to start
> with
>
> It should be fine. All that will happen is that CURRENT_DATE + 30 will be
> in the next
> year. I don't know exactly how DBISAM's SQL does the comparison but if its
> like Delphi the
> date will be transformed into an offset from a base date and the resulting
> offsets compared.
>
> Roy Lambert [Team Elevate]
>

Dave

> Quick question. Will this still work if the next birthday is in the next
year, not the
> current year?
>
snip
>
> WHERE CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS
CHAR(4))+SUBSTRING(CAST(datefield AS
> CHAR(10)) FROM 5) AS DATE) BETWEEN CURRENT_DATE AND CURRENT_DATE + 30

I think you have to check the next year as well to catch birthdates in
January when the process is run in December ie add

OR
CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE)+1 AS
CHAR(4))+SUBSTRING(CAST(datefield AS
CHAR(10)) FROM 5) AS DATE) BETWEEN CURRENT_DATE AND CURRENT_DATE + 30


John

....
also, it's going to fail if the birth date is Feb,29 and the current
year is not a leap year.

--
Fernando Dias
[Team Elevate]