Got this nearly worked out now, but somehow I can't get the nested case when
syntax right:
SELECT
case
when
date('2006-10-14', '+' || (strftime('%Y', 'now') - strftime('%Y',
'2006-10-14')) || ' years') <= date('now')
then
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14')) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14')) - 1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14'))) * 12 +
(strftime('%m', 'now') - strftime('%m', '2006-10-14'))
end
else
case when
strftime('%d', 'now') > strftime('%d', '2006-10-14')
then
((strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14')))) -1
else
(strftime('%Y', 'now') - strftime('%Y', '2006-10-14') -1) * 12 +
(strftime('%m', 'now') +
(12 - strftime('%m', '2006-10-14')))
end
end
It will give me an error (from my VB wrapper) syntax error near else.
Any idea what is wrong here?
RBS
-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: [email protected]
Subject: Re: [sqlite] Age calculation on literal
RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form 'yyyy-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when
> date('2002-01-01', '+' || (strftime('%Y', 'now') -
> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
> then
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
> end
>
> Why is this?
>
>
This works for me in the sqlite shell as shown below:
C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
...> case when
...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
...> then
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
...> else
...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
...> end
...> ;
5
How are you running this query?
Dennis Cote
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
