Re: [sqlite] calculate age (off topic)
Jay Sprenkle wrote:
> LOL! You should look at a function to determine if a day is a holiday.
> Talk about ugly! In some places you literally need to know the weather
> and the phase of the moon!
>
> --
[EMAIL PROTECTED] ~/movie]$ pom
The Moon is Waning Gibbous (100% of Full)
[EMAIL PROTECTED] ~/movie]$ which pom
/usr/games/pom
is there a shell script program to let me know if the weather is clear
in mecca? ;)
wget -O -
"http://www.weather.com/outlook/travel/businesstraveler/local/SAXX0013?from=search_current;
| \
grep "B CLASS=obsTextA" | \
grep -v "\" | \
awk -F "<" "{ print \$5 }" | \
awk -F ">" "{ print \$2 }"
Today it returns "Party Cloudy".
Solution is non optimal. There is probably a much better way to do it
in perl, but I'm too lazy to try right now. Subject to break whenever
weather.com's webmaster change their HTML layout. Tested on an
up-to-date Gentoo Linux (2006.1)
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
> LOL! You should look at a function to determine if a day is a holiday. > Talk about ugly! In some places you literally need to know the weather > and the phase of the moon! > > -- [EMAIL PROTECTED] ~/movie]$ pom The Moon is Waning Gibbous (100% of Full) [EMAIL PROTECTED] ~/movie]$ which pom /usr/games/pom is there a shell script program to let me know if the weather is clear in mecca? ;) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] calculate age
Jay Sprenkle wrote: LOL! You should look at a function to determine if a day is a holiday. Talk about ugly! In some places you literally need to know the weather and the phase of the moon! -- [EMAIL PROTECTED] ~/movie]$ pom The Moon is Waning Gibbous (100% of Full) [EMAIL PROTECTED] ~/movie]$ which pom /usr/games/pom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] calculate age
I see Microsoft is already offering a patch for Windows XP to handle the new U.S. DST rules. -Clark - Original Message From: Joe Wilson <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Saturday, December 23, 2006 9:52:02 AM Subject: Re: [sqlite] calculate age Holiday determination per country (or even per state/city) via an algorithm can only get you so far because it is at the whim of constantly changing local laws. You basically need a database of all the dates for the exceptions. Even getting a reliable source of such information for various countries is problematic. On a somewhat related topic, I wonder how many computer systems will be affected by the new US daylight savings time changes going into effect in 2007. What a mess. Son of Y2K - Y2K7DST. --- Jay Sprenkle <[EMAIL PROTECTED]> wrote: > LOL! You should look at a function to determine if a day is a holiday. > Talk about ugly! In some places you literally need to know the weather > and the phase of the moon! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] calculate age
Unfortunately our Congress seems to have a short memory, on top of constantly appearing embarrassingly stupid and crooked as a snake. I remember the last time they messed with DST. Seems little Yankee school munchkins were being squashed by school buses in the resulting early morning darkness. Didn't take long for the voter outcry to reverse that dumb a-- move :-( Note to silicon designers: Don't rush to adjust! Fred > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Saturday, December 23, 2006 11:52 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] calculate age > > > Holiday determination per country (or even per state/city) > via an algorithm > can only get you so far because it is at the whim of > constantly changing > local laws. You basically need a database of all the dates > for the exceptions. > Even getting a reliable source of such information for > various countries is > problematic. > > On a somewhat related topic, I wonder how many computer > systems will be affected > by the new US daylight savings time changes going into effect in 2007. > What a mess. Son of Y2K - Y2K7DST. > > --- Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > LOL! You should look at a function to determine if a day is > a holiday. > > Talk about ugly! In some places you literally need to know > the weather > > and the phase of the moon! > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] calculate age
Holiday determination per country (or even per state/city) via an algorithm can only get you so far because it is at the whim of constantly changing local laws. You basically need a database of all the dates for the exceptions. Even getting a reliable source of such information for various countries is problematic. On a somewhat related topic, I wonder how many computer systems will be affected by the new US daylight savings time changes going into effect in 2007. What a mess. Son of Y2K - Y2K7DST. --- Jay Sprenkle <[EMAIL PROTECTED]> wrote: > LOL! You should look at a function to determine if a day is a holiday. > Talk about ugly! In some places you literally need to know the weather > and the phase of the moon! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] calculate age
On 12/23/06, Joe Wilson <[EMAIL PROTECTED]> wrote: Warning - Thar Be Dragons Here! The definition of a month is more or less arbitrary depending on the situation. Trying to define what exactly is the duration of a "month" is a bottomless pit of endless bickering best decided by druids, popes and historians - certainly beyond the scope of SQLite. Some manufacturers even employ 13 "month" years for production planning/scheduling because thirteen 28-day months /almost/ fit into a 365-day year. There are many other functions with very well-defined/accepted semantics that are more worthy of consideration as a core SQLite function. LOL! You should look at a function to determine if a day is a holiday. Talk about ugly! In some places you literally need to know the weather and the phase of the moon! -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] calculate age
Warning - Thar Be Dragons Here!
The definition of a month is more or less arbitrary depending on the situation.
Trying to define what exactly is the duration of a "month" is a bottomless
pit of endless bickering best decided by druids, popes and historians -
certainly
beyond the scope of SQLite. Some manufacturers even employ 13 "month" years
for production planning/scheduling because thirteen 28-day months /almost/
fit into a 365-day year.
There are many other functions with very well-defined/accepted semantics
that are more worthy of consideration as a core SQLite function.
--- [EMAIL PROTECTED] wrote:
> "RB Smissaert" <[EMAIL PROTECTED]> wrote:
> > Is there a way to calculate the age given the 2 dates in the standard format
> > -mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
> > by 365, but that won't be accurate enough.
> > It would be easy to calculate the age in the application and update the
> > database, but I prefer to do it all in SQL if I can.
> >
>
> Suppose there were a MONTHSPAN() function that took two dates and
> returns the number of months between them. You could then compute
> the age by dividing monthspan() by 12.0 and taking the integer part.
>
> I took a stab at writing a monthspan() function. Sometimes the
> results seem a bit unusual:
>
> SELECT monthspan('2007-01-01','2006-01-01') -> 12.0 OK
> SELECT monthspan('2006-03-01','2006-01-01') -> 2.0 OK
> SELECT monthspan('2006-03-31','2006-01-31') -> 2.0 OK
> SELECT monthspan('2006-04-01','2006-02-01') -> 2.0 OK
> SELECT monthspan('2006-04-30','2006-02-28') -> 2.064516 H
>
> The algorithm causes the result to jump up to the next integer
> value as you pass the anniversary date of each month. This leads
> to some seemingly strange results on shorter months. But I
> suppose you are always going to get that when you are trying
> to do difference calculations on months of differing lengths.
>
> Here is the code. I have not checked it in. I'm not sure it
> is such a good idea.
>
> RCS file: /sqlite/sqlite/src/date.c,v
> retrieving revision 1.58
> diff -u -r1.58 date.c
> --- date.c 25 Sep 2006 18:05:04 - 1.58
> +++ date.c 22 Dec 2006 19:25:00 -
> @@ -747,6 +747,27 @@
> }
>
> /*
> +**monthspan( TIMESTRING1, TIMESTRING2 )
> +**
> +** Return the number of months from the second date to the first
> +*/
> +static void monthspanFunc(
> + sqlite3_context *context,
> + int argc,
> + sqlite3_value **argv
> +){
> + DateTime date1, date2;
> + if( argc==2 && isDate(1, argv, )==0 && isDate(1, [1],
> )==0 )
> {
> +double m1, m2;
> +computeYMD();
> +computeYMD();
> +m1 = date1.Y*12.0 + date1.M + (date1.D-1)/31.0;
> +m2 = date2.Y*12.0 + date2.M + (date2.D-1)/31.0;
> +sqlite3_result_double(context, m1 - m2);
> + }
> +}
> +
> +/*
> **strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
> **
> ** Return a string described by FORMAT. Conversions as follows:
> @@ -997,6 +1018,7 @@
> { "time", -1, timeFunc},
> { "datetime", -1, datetimeFunc},
> { "strftime", -1, strftimeFunc},
> +{ "monthspan", 2, monthspanFunc },
> { "current_time", 0, ctimeFunc },
> { "current_timestamp", 0, ctimestampFunc },
> { "current_date", 0, cdateFunc },
>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Can confirm now that mine didn't work and yours does.
Nice work!
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> I think I got it now:
>
> select
> (date('now') - '2002-12-22') -
> ((julianday('now', '-' ||
> (date('now') - '2002-12-22') ||
> 'year') <
> julianday('2002-12-22')))
>
> seems to work.
>
>
>
This is either clever or foolhardy depending upon how you look at it.
It works only because sqlite stops at the first illegal character (the
-) when converting a string to an integer as it tries to subtract the
two date stings.
(date('now') - '2002-12-22')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depending upon the actual date.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Yes, a function like that would be useful.
Unless I am mistaken I would have to wait though till the maker of my VB
wrapper would incorporate it.
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:26
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there a way to calculate the age given the 2 dates in the standard
format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and
divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
Suppose there were a MONTHSPAN() function that took two dates and
returns the number of months between them. You could then compute
the age by dividing monthspan() by 12.0 and taking the integer part.
I took a stab at writing a monthspan() function. Sometimes the
results seem a bit unusual:
SELECT monthspan('2007-01-01','2006-01-01') -> 12.0 OK
SELECT monthspan('2006-03-01','2006-01-01') -> 2.0 OK
SELECT monthspan('2006-03-31','2006-01-31') -> 2.0 OK
SELECT monthspan('2006-04-01','2006-02-01') -> 2.0 OK
SELECT monthspan('2006-04-30','2006-02-28') -> 2.064516 H
The algorithm causes the result to jump up to the next integer
value as you pass the anniversary date of each month. This leads
to some seemingly strange results on shorter months. But I
suppose you are always going to get that when you are trying
to do difference calculations on months of differing lengths.
Here is the code. I have not checked it in. I'm not sure it
is such a good idea.
RCS file: /sqlite/sqlite/src/date.c,v
retrieving revision 1.58
diff -u -r1.58 date.c
--- date.c 25 Sep 2006 18:05:04 - 1.58
+++ date.c 22 Dec 2006 19:25:00 -
@@ -747,6 +747,27 @@
}
/*
+**monthspan( TIMESTRING1, TIMESTRING2 )
+**
+** Return the number of months from the second date to the first
+*/
+static void monthspanFunc(
+ sqlite3_context *context,
+ int argc,
+ sqlite3_value **argv
+){
+ DateTime date1, date2;
+ if( argc==2 && isDate(1, argv, )==0 && isDate(1, [1],
)==0 )
{
+double m1, m2;
+computeYMD();
+computeYMD();
+m1 = date1.Y*12.0 + date1.M + (date1.D-1)/31.0;
+m2 = date2.Y*12.0 + date2.M + (date2.D-1)/31.0;
+sqlite3_result_double(context, m1 - m2);
+ }
+}
+
+/*
**strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT. Conversions as follows:
@@ -997,6 +1018,7 @@
{ "time", -1, timeFunc},
{ "datetime", -1, datetimeFunc},
{ "strftime", -1, strftimeFunc},
+{ "monthspan", 2, monthspanFunc },
{ "current_time", 0, ctimeFunc },
{ "current_timestamp", 0, ctimestampFunc },
{ "current_date", 0, cdateFunc },
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
> This is either clever or foolhardy depending upon how you look at it.
Probably the last, so will test a bit and then maybe switch to your method.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> I think I got it now:
>
> select
> (date('now') - '2002-12-22') -
> ((julianday('now', '-' ||
> (date('now') - '2002-12-22') ||
> 'year') <
> julianday('2002-12-22')))
>
> seems to work.
>
>
>
This is either clever or foolhardy depending upon how you look at it.
It works only because sqlite stops at the first illegal character (the
-) when converting a string to an integer as it tries to subtract the
two date stings.
(date('now') - '2002-12-22')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depending upon the actual date.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Is there a way to calculate the age given the 2 dates in the standard format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
Suppose there were a MONTHSPAN() function that took two dates and
returns the number of months between them. You could then compute
the age by dividing monthspan() by 12.0 and taking the integer part.
I took a stab at writing a monthspan() function. Sometimes the
results seem a bit unusual:
SELECT monthspan('2007-01-01','2006-01-01') -> 12.0 OK
SELECT monthspan('2006-03-01','2006-01-01') -> 2.0 OK
SELECT monthspan('2006-03-31','2006-01-31') -> 2.0 OK
SELECT monthspan('2006-04-01','2006-02-01') -> 2.0 OK
SELECT monthspan('2006-04-30','2006-02-28') -> 2.064516 H
The algorithm causes the result to jump up to the next integer
value as you pass the anniversary date of each month. This leads
to some seemingly strange results on shorter months. But I
suppose you are always going to get that when you are trying
to do difference calculations on months of differing lengths.
Here is the code. I have not checked it in. I'm not sure it
is such a good idea.
RCS file: /sqlite/sqlite/src/date.c,v
retrieving revision 1.58
diff -u -r1.58 date.c
--- date.c 25 Sep 2006 18:05:04 - 1.58
+++ date.c 22 Dec 2006 19:25:00 -
@@ -747,6 +747,27 @@
}
/*
+**monthspan( TIMESTRING1, TIMESTRING2 )
+**
+** Return the number of months from the second date to the first
+*/
+static void monthspanFunc(
+ sqlite3_context *context,
+ int argc,
+ sqlite3_value **argv
+){
+ DateTime date1, date2;
+ if( argc==2 && isDate(1, argv, )==0 && isDate(1, [1], )==0 )
{
+double m1, m2;
+computeYMD();
+computeYMD();
+m1 = date1.Y*12.0 + date1.M + (date1.D-1)/31.0;
+m2 = date2.Y*12.0 + date2.M + (date2.D-1)/31.0;
+sqlite3_result_double(context, m1 - m2);
+ }
+}
+
+/*
**strftime( FORMAT, TIMESTRING, MOD, MOD, ...)
**
** Return a string described by FORMAT. Conversions as follows:
@@ -997,6 +1018,7 @@
{ "time", -1, timeFunc},
{ "datetime", -1, datetimeFunc},
{ "strftime", -1, strftimeFunc},
+{ "monthspan", 2, monthspanFunc },
{ "current_time", 0, ctimeFunc },
{ "current_timestamp", 0, ctimestampFunc },
{ "current_date", 0, cdateFunc },
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Yes, thanks that works.
Will do a bit of testing to see which one is the fastest.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 19:05
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> Thanks, it is getting close, but it doesn't quite work.
>
> For example this:
>
> select
> case
> when date('2002-12-22', '+' ||
> strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
> ' years') >= date('now')
> then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
> else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
> end
> as age
>
> Gives 3
>
>
Oops, I needed another set of brackets to ensure correct order of
operations, and I had the comparison wrong (need <= not >=).
This works:
select
case
when date('2002-12-22', '+' ||
(strftime('%Y', 'now') - strftime('%Y', '2002-12-22')) ||
' years') <= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
RB Smissaert wrote:
I think I got it now:
select
(date('now') - '2002-12-22') -
((julianday('now', '-' ||
(date('now') - '2002-12-22') ||
'year') <
julianday('2002-12-22')))
seems to work.
This is either clever or foolhardy depending upon how you look at it.
It works only because sqlite stops at the first illegal character (the
-) when converting a string to an integer as it tries to subtract the
two date stings.
(date('now') - '2002-12-22')
becomes
'2006-12-22' - '2002-12-22'
which is converted to
2006 - 2002
without any warning or error and produces the correct result 4
Other than that it uses the reverse logic of my case statement. Get the
difference of the years and subtract 1 or 0 depending upon the actual date.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
RB Smissaert wrote:
Thanks, it is getting close, but it doesn't quite work.
For example this:
select
case
when date('2002-12-22', '+' ||
strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
Gives 3
Oops, I needed another set of brackets to ensure correct order of
operations, and I had the comparison wrong (need <= not >=).
This works:
select
case
when date('2002-12-22', '+' ||
(strftime('%Y', 'now') - strftime('%Y', '2002-12-22')) ||
' years') <= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
I think I got it now:
select
(date('now') - '2002-12-22') -
((julianday('now', '-' ||
(date('now') - '2002-12-22') ||
'year') <
julianday('2002-12-22')))
seems to work.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 18:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> Is there a way to calculate the age given the 2 dates in the standard
format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and
divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
> RBS
>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
>
>
Try this (untested).
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', dob)
else strftime('%Y', 'now') - strftime('%Y', dob) - 1
end
as age
from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
Thanks, it is getting close, but it doesn't quite work.
For example this:
select
case
when date('2002-12-22', '+' ||
strftime('%Y', 'now') - strftime('%Y', '2002-12-22') ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', '2002-12-22')
else strftime('%Y', 'now') - strftime('%Y', '2002-12-22') -1
end
as age
Gives 3
I will try a bit further with my construction.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 18:12
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] calculate age
RB Smissaert wrote:
> Is there a way to calculate the age given the 2 dates in the standard
format
> -mm-dd? I could do julianday('now') - julianday(dateofbirth) and
divide
> by 365, but that won't be accurate enough.
> It would be easy to calculate the age in the application and update the
> database, but I prefer to do it all in SQL if I can.
>
> RBS
>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-
>
>
>
Try this (untested).
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', dob)
else strftime('%Y', 'now') - strftime('%Y', dob) - 1
end
as age
from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] calculate age
This 'hard-coded' SQL does the job:
select
(date('now') - '2002-12-23') -
((julianday('now', '-4year') < julianday('2002-12-23')))
But the trouble is to get the value 4 here.
This value should be date('now') - '2002-12-23'
But I can't get the right syntax to achieve that.
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 22 December 2006 17:03
To: sqlite-users@sqlite.org
Subject: [sqlite] calculate age
Is there a way to calculate the age given the 2 dates in the standard format
-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
by 365, but that won't be accurate enough.
It would be easy to calculate the age in the application and update the
database, but I prefer to do it all in SQL if I can.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] calculate age
RB Smissaert wrote:
Is there a way to calculate the age given the 2 dates in the standard format
-mm-dd? I could do julianday('now') - julianday(dateofbirth) and divide
by 365, but that won't be accurate enough.
It would be easy to calculate the age in the application and update the
database, but I prefer to do it all in SQL if I can.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Try this (untested).
select
case
when date(dob, '+' ||
strftime('%Y', 'now') - strftime('%Y', dob) ||
' years') >= date('now')
then strftime('%Y', 'now') - strftime('%Y', dob)
else strftime('%Y', 'now') - strftime('%Y', dob) - 1
end
as age
from t;
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
