RE: [sqlite] Age calculation on literal
This function works and despite the convoluted construction it is pretty
fast, takes about 0.5 sec to convert one field in about 25000 rows.
I thought I can speed it up by replacing all the SQLite functions that
produce a literal by a VB variable. Indeed with this I can get it down to
about 0.3 secs. Does this mean that SQLite recalculates these functions
multiple times for every row? I suppose it does and in a way it makes sense
as 'now' changes over time.
In case any VB user is interested in this:
Function CurrentDateISO8601() As String
Dim lCY As Long
Dim lCM As Long
Dim lCD As Long
Dim strZeroMonth As String
Dim strZeroDay As String
lCY = Year(Date)
lCM = Month(Date)
lCD = Day(Date)
If lCM < 10 Then
strZeroMonth = "0"
End If
If lCD < 10 Then
strZeroDay = "0"
End If
CurrentDateISO8601 = "'" & lCY & "-" & _
strZeroMonth & lCM & "-" & _
strZeroDay & lCD & "'"
End Function
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
Dim strCDate As String
Dim lCM As Long
Dim lCD As Long
Dim strCY As String
Dim strCM As String
Dim strCD As String
lCM = Month(Date)
lCD = Day(Date)
strCY = "'" & CStr(Year(Date)) & "'"
If lCM < 10 Then
strCM = "'0" & CStr(lCM) & "'"
Else
strCM = "'" & CStr(lCM) & "'"
End If
If lCD < 10 Then
strCD = "'0" & CStr(lCD) & "'"
Else
strCD = "'" & CStr(lCD) & "'"
End If
strCDate = CurrentDateISO8601()
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = _
"case when " & strCDate & " >= " & _
"date(" & strField & ", '+' || " & strCY & " - " & _
"strftime('%Y', " & strField & ") || ' years') then " & _
"case when " & strCD & " < strftime('%d', " & strField & ") then " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ")) * 12 + " & _
"(" & strCM & " - strftime('%m', " & strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & strCD & " < strftime('%d', " & strField & ") then " & _
"((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
"(" & strCM & " + (12 - strftime('%m', " & strField & " - 1 " & _
"else " & _
"((" & strCY & " - strftime('%Y', " & strField & ") - 1) * 12 + " & _
"(" & strCM & " + (12 - strftime('%m', " & strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias
End Function
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 23:45
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal
Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField
RE: [sqlite] Age calculation on literal
Got the syntax right, but not the logic.
I believe this (VB) function will now get the right SQL to get the age in
months:
Function ISO8601Date2AgeInMonths(strField As String, _
Optional strAlias As String) As String
Dim strAS As String
If Len(strAlias) > 0 Then
strAS = " AS "
End If
ISO8601Date2AgeInMonths = "case when date('now') >= " & _
"date(" & strField & ", '+' || (strftime('%Y',
'now') - " & _
"strftime('%Y', " & strField & ")) || ' years')
then " & _
"case when strftime('%d', 'now') <
strftime('%d', " & strField & ") then " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ")) * 12 + " & _
"(strftime('%m', 'now') - strftime('%m', " &
strField & "))) - 0 " & _
"end " & _
"else " & _
"case when " & _
"strftime('%d', 'now') < strftime('%d', " &
strField & ") " & _
"then " & _
"(strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & "))) - 1 " & _
"else " & _
"((strftime('%Y', 'now') - strftime('%Y', " &
strField & ") - 1) * 12 + " & _
"(strftime('%m', 'now') + (12 - strftime('%m', "
& strField & " - 0 " & _
"End " & _
"End" & strAS & strAlias
End Function
RBS
-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 21:46
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Age calculation on literal
Got this now, after correcting the brackets:
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
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> 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 +
You have an extra closing bracket in the line above.
(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
RE: [sqlite] Age calculation on literal
Got this now, after correcting the brackets:
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
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> 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 +
You have an extra closing bracket in the line above.
(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?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
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
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Age calculation on literal
Yes, that looks better and thanks for that.
Still get the same error though.
I will keep fiddling with it.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 June 2007 19:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
>
> 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 +
You have an extra closing bracket in the line above.
(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?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
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
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Age calculation on literal
On 6/1/07, RB Smissaert <[EMAIL PROTECTED]> wrote:
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 +
You have an extra closing bracket in the line above.
(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?
Try this instead, I find the extra indentation makes it easier to see what
you are doing.
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
HTH
Dennis Cote
RE: [sqlite] Age calculation on literal
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: sqlite-users@sqlite.org
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 '-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]
-
RE: [sqlite] Age calculation on literal
> How are you running this query?
I am running this from Excel VBA with a free wrapper from Olaf Schmidt,
dhSQLite, based on 3.3.17. I will check my code, but can't think of a way
why it should add 100 with literals and not on table fields.
Must admit I have been wrong before with these kind of things ...
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
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 '-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]
-
RE: [sqlite] Age calculation on literal
Sorry, forget about this, it was something in the VBA code, so nil to do
with SQLite.
Could I ask you how I would get the age in months? I can see it will be
along similar lines, but maybe you have worked it out already.
I need it to be full calendar months, so, if current date is 2007-05-31 then
DOB Age in months
--
2007-05-01 0
2007-04-30 1
2007-01-01 4
Etc.
RBS
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
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 '-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]
-
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 '-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]
-
