SQL needs AGE function.

Hello,

I want to select people from a database within a certain age range.

For example:

Select * From TablePerson Where (Age(Birthdate) > 20) and (Age(Birthdate) <
98)

That would make it really easy.

Things I have tried so far:

Trail 1, SQL DateDiff function:

DateDiff('yyyy', '31-12-1910', '6-1-2008' )

This is no good, it returns age 98, the real age is 97.

Trail 2, Encoding min range and max range as TdateTime in Delphi.

EncodeDate( 20, 0, 0 ); // failure, invalid year specified.

I either need a solution in SQL or in Delphi.

The only solution I can think of in Delphi is:

Calculate the minimum birthday and maximum birthday, so then the question
becomes:

How to do that accurately ?

Thanks for listening and any help,

Bye,
Skybuck.

P.S.: alt.php.sql newsgroup included because maybe they know something about
SQL and maybe somebody can pass this request onto database vendors ;) :) =D

Re: SQL needs AGE function.

Skybuck Flying wrote:
> Hello,
>
> I want to select people from a database within a certain age range.
>
> For example:
>
> Select * From TablePerson Where (Age(Birthdate) > 20) and (Age(Birthdate) <
> 98)
>
> That would make it really easy.
>
> Things I have tried so far:
>
> Trail 1, SQL DateDiff function:
>
> DateDiff('yyyy', '31-12-1910', '6-1-2008' )
>
> This is no good, it returns age 98, the real age is 97.

Tried PERIOD_DIFF(x,y)/12 ?


--

//Aho

Re: SQL needs AGE function.

Ok,

I think I have figured out a method to do what I want in Delphi:

(Which might be usefull for any other programming languages as well):

Short story:

Decode the date of now, subtract the age range to get minimum and maximum
decoded birthdays.

Re-encode minimum and maximum birthdates, select birthday range.

Copy of posting:
"
Suppose somebody tells you the following information:

On 6 january 2008 I was exactly 97 years.

The mission is to calculate the date of birth.

This is probably very simple:

Simply subtract 97 from 2008

So his birthdate would be: 6 january 1911

Now given a set of birthdates find the people from age 0 and 97.

Suppose now is 6 january 2009. (Guy is now 98)

Subtract 97 from it.

Maximum range: 6 january 1912.

Scan birthdays from 6 january 1912 to now.

His birthday was at 6 january 1911, so he out of range.

Ok, I think this technique could work,

Just decode now, subtract year range from it, re-encode, use it to select
birthdays.

(Related to previous thread: SQL needs AGE function) ;)
"

Bye,
Skybuck.

Re: SQL needs AGE function.

Skybuck

believe me and others. there is a way to do this
and the vendors do not need to be informed. - -
you just need to take the time to think about it and
how it is typically done.

the fact that you do not know it - - - does not mean that a method does
not exist.

and the reason that you can not pass this on yourself and have to count
on the work and effort of others. - - - Is your copy of PHP legal?

Skybuck Flying wrote:
> Hello,
>
> I want to select people from a database within a certain age range.
>
> For example:
>
> Select * From TablePerson Where (Age(Birthdate) > 20) and (Age(Birthdate) <
> 98)
>
> That would make it really easy.
>
> Things I have tried so far:
>
> Trail 1, SQL DateDiff function:
>
> DateDiff('yyyy', '31-12-1910', '6-1-2008' )
>
> This is no good, it returns age 98, the real age is 97.
>
> Trail 2, Encoding min range and max range as TdateTime in Delphi.
>
> EncodeDate( 20, 0, 0 ); // failure, invalid year specified.
>
> I either need a solution in SQL or in Delphi.
>
> The only solution I can think of in Delphi is:
>
> Calculate the minimum birthday and maximum birthday, so then the question
> becomes:
>
> How to do that accurately ?
>
> Thanks for listening and any help,
>
> Bye,
> Skybuck.
>
> P.S.: alt.php.sql newsgroup included because maybe they know something about
> SQL and maybe somebody can pass this request onto database vendors ;) :) =D
>
>

Re: SQL needs AGE function.

Only problem remaining is comparing dates with strings (ms access):

I tried:

where (Birthday > '01-01-1970')

I tried:

where (Birthday > "01-01-1970")

MS ACCESS or maybe SQL wants the date in a certain format ?

Hmmm...

Maybe there is a function to convert any possible date format ?

Bye,
Skybuck.

Re: SQL needs AGE function.

Skybuck Flying wrote:
> Only problem remaining is comparing dates with strings (ms access):

I should recommend you took a look at the microsoft-access documentation.

> I tried:
>
> where (Birthday > '01-01-1970')
>
> I tried:
>
> where (Birthday > "01-01-1970")
>
> MS ACCESS or maybe SQL wants the date in a certain format ?

In MySQL you do

WHERE Birthday>19700101

> Maybe there is a function to convert any possible date format ?

DATE_FORMAT


--

//Aho

Re: SQL needs AGE function.

Ok, this weblogger guy writes:

"
Date values in SQL is always saved in "MM/DD/YY(YY)" format.
Here's a line of code to get the string in the correct format for SQL:
FormatDateTime('mm"/"dd"/"yyyy', Now)
"

So I tried:

where (Birthday > 01/01/1970)

Seems to be working so far.

So far so good.

Soon I'll post some nice code, for age range selecting ;)

Bye,
Skybuck.

Re: SQL needs AGE function.

I propose AGE( Birthday )

Do you know an easier way Jim ? ;)

Bye,
Skybuck.

Re: SQL needs AGE function. Age range selection Delphi/SQL/MS ACCESS solution.

Delphi snippet:

DecodeDate( Now, vNowYear, vNowMonth, vNowDay );

vMinimumAge := 5;
vMaximumAge := 29;
vMinimumBirthDate := FormatDateTime('mm"/"dd"/"yyyy', EncodeDate(
vNowYear - (vMaximumAge+1), vNowMonth, vNowDay ) );

vMaximumBirthDate := FormatDateTime('mm"/"dd"/"yyyy', EncodeDate(
vNowYear - (vMinimumAge+1), vNowMonth, vNowDay ) );

SQL snippit:

AND
(
(BirthDate <> null) AND
(BirthDate > #01/01/1970#) AND
(BirthDate < #20/20/1980#)
);

Notes:

Surround date strings as follows for ms access date comparisions:

#date string#

Bye,
Skybuck.

Re: SQL needs AGE function. Age range selection Delphi/SQL/MS ACCESS solution.

Skybuck Flying wrote:
> Delphi snippet:
>
> DecodeDate( Now, vNowYear, vNowMonth, vNowDay );
>
> vMinimumAge := 5;
> vMaximumAge := 29;
> vMinimumBirthDate := FormatDateTime('mm"/"dd"/"yyyy', EncodeDate(
> vNowYear - (vMaximumAge+1), vNowMonth, vNowDay ) );
>
> vMaximumBirthDate := FormatDateTime('mm"/"dd"/"yyyy', EncodeDate(
> vNowYear - (vMinimumAge+1), vNowMonth, vNowDay ) );
>
> SQL snippit:
>
> AND
> (
> (BirthDate <> null) AND
> (BirthDate > #01/01/1970#) AND
> (BirthDate < #20/20/1980#)
> );
>
> Notes:
>
> Surround date strings as follows for ms access date comparisions:
>
> #date string#
>
> Bye,
> Skybuck.

This doesn't look like PHP to me???

Re: SQL needs AGE function.

Skybuck Flying wrote:
> I propose AGE( Birthday )
>
> Do you know an easier way Jim ? ;)
>
> Bye,
> Skybuck.
>
>
sure write your own function


Jim P

Re: SQL needs AGE function.

Sean Cleary wrote:

> On Jan 6, 12:50 am, "J.O. Aho" wrote:
> //Aho
> Cartwright or as you know him (Aho) has been trying to shut down a
> good google group.

Please, before you start to accuse people, do better research and you will
notice that there are more than one person using user@example.net/.com. The
person whom you accuse me to be uses "no cache" header, so that his posts will
be deleted after a while from the NNTP, you don't find this on my posts.

Please cancel all your posts where you accuse me to be someone whom I'm not.


--

//Aho

Re: SQL needs AGE function.

In article ,
spam@hotmail.com says...
> Ok, this weblogger guy writes:
>
> "
> Date values in SQL is always saved in "MM/DD/YY(YY)" format.

He's wrong. It may be true for some SQL engines, but not all.

> Here's a line of code to get the string in the correct format for SQL:
> FormatDateTime('mm"/"dd"/"yyyy', Now)

Only good for Access. Most client-server engines want either yyyymmdd
or yyyy-mm-dd (some will take either).

> "
>
> So I tried:
>
> where (Birthday > 01/01/1970)
>
> Seems to be working so far.
>
> So far so good.
>
> Soon I'll post some nice code, for age range selecting ;)
>
> Bye,
> Skybuck.
>
>
>

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Re: SQL needs AGE function.

Skybuck Flying wrote:
> Ok, this weblogger guy writes:
>
> "
> Date values in SQL is always saved in "MM/DD/YY(YY)" format.

He is talking out of his arse.

Date values are stored in database tables in whatever internal format the
designers choose. It may well be in UNIX datetime format, but it is likely
to be anything.

Rather than looking at what weblogger guys write, why not use the manual for
whatever product you are using.

Oh that's right, you don't want to use the manual for something as "simple"
as dates, you want to spend hours getting it wrong. Silly me, I forgot!

Re: SQL needs AGE function.

"Paul Lautman" wrote in message
news:5uf0iaF1hvg1sU1@mid.individual.net...
> Skybuck Flying wrote:
>> Ok, this weblogger guy writes:
>>
>> "
>> Date values in SQL is always saved in "MM/DD/YY(YY)" format.
>
> He is talking out of his arse.
>
> Date values are stored in database tables in whatever internal format the
> designers choose. It may well be in UNIX datetime format, but it is likely to
> be anything.

Precisely.. The internal format is going to be whatever the designer made.
Presentation format is determined at runtime (for coding) and in Display Format
(for reporting), all chosen based on the requirements of the presenter..

> Rather than looking at what weblogger guys write, why not use the manual for
> whatever product you are using.
>
> Oh that's right, you don't want to use the manual for something as "simple"
> as dates, you want to spend hours getting it wrong. Silly me, I forgot!
>