 |
|
|
|
Next: how can i put multiple statmwnts in sum(case...
|
| Author |
Message |
External
Since: Jan 06, 2008
Posts: 16
|
(Msg. 1) Posted: Sun Jan 06, 2008 5:09 am
Post subject: SQL needs AGE function.
Archived from groups: alt>comp>lang>borland-delphi, others (more info?)
|
|
|
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) <
9 
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 |
|
| Back to top |
|
 | |
External
Since: Jan 06, 2008
Posts: 5
|
(Msg. 2) Posted: Sun Jan 06, 2008 5:09 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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) <
> 9
>
> 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
>
> |
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 16
|
(Msg. 3) Posted: Sun Jan 06, 2008 5:09 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 9
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. >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Dec 01, 2003
Posts: 397
|
(Msg. 4) Posted: Sun Jan 06, 2008 5:09 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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) <
> 9
>
> 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 >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 16
|
(Msg. 5) Posted: Sun Jan 06, 2008 5:09 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 16
|
(Msg. 6) Posted: Sun Jan 06, 2008 5:09 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Dec 01, 2003
Posts: 397
|
(Msg. 7) Posted: Sun Jan 06, 2008 5:09 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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 >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 16
|
(Msg. 8) Posted: Sun Jan 06, 2008 8:00 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 16
|
(Msg. 9) Posted: Sun Jan 06, 2008 8:00 am
Post subject: Re: SQL needs AGE function. Age range selection Delphi/SQL/MS ACCESS solution. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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. >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 3
|
(Msg. 10) Posted: Sun Jan 06, 2008 10:07 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: alt>comp>lang>borland-delphi, others (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 3
|
(Msg. 11) Posted: Sun Jan 06, 2008 10:29 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Feb 28, 2006
Posts: 55
|
(Msg. 12) Posted: Sun Jan 06, 2008 10:59 am
Post subject: Re: SQL needs AGE function. Age range selection Delphi/SQL/MS ACCESS solution. [Login to view extended thread Info.]
Archived from groups: alt>comp>lang>borland-delphi, others (more info?)
|
|
|
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??? >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 5
|
(Msg. 13) Posted: Sun Jan 06, 2008 11:34 am
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jan 06, 2008
Posts: 3
|
(Msg. 14) Posted: Sun Jan 06, 2008 4:21 pm
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: alt>comp>lang>borland-delphi, others (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Dec 01, 2003
Posts: 397
|
(Msg. 15) Posted: Sun Jan 06, 2008 4:59 pm
Post subject: Re: SQL needs AGE function. [Login to view extended thread Info.]
Archived from groups: alt>comp>lang>borland-delphi, others (more info?)
|
|
|
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 RemoveThis @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 >> Stay informed about: SQL needs AGE function. |
|
| Back to top |
|
| |
| Related Topics: | copy function Q - Can anyone tell me why this isn't working.. I am sure its just something stupid but I can't see it yet. <?PHP function uploadpic($pic){ foreach($pic as $value){ copy ($_FILES['$value']['tmp_name'], $_FILES['$value']['name']) or die...
Simple php MySQL function - Hello I looking for a php /SQL function that should show the next or previous record from a SQL table. At the bottom of the window I will put two buttons with the following picture < >. This function should respect the alphabetic order from on...
PASSWORD function in MySQL - How big should a password field be in a table when using the MySQL PASSWORD function? Can I calculate how big the hashed value will be (in terms of number of characters) if I limit the user to say 16 character passwords? Thanks, Rick..
Newbie needs help with mail function - Hi, I've got so far with the mail() function but it won't work. Please help. WHere am I going wrong? Kind regards, Raj (Newbie) $to="def@ghi.com"; $email_system="abc@def.com"; $message="Hi"; $headers = "From: &quo...
Mail function sends to gmail but not hotmail. Please help .. - Hi, Can anyone tell me what the requirements are to allow hotmail to collect messages. I have a script using the mail function at the moment that emails most email clients but the email doesn't seem to be accepted by hotmail. It email doesn;t even go... |
|
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
