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 & ")) * 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
>
> 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]
----------------------------------------------------------------------------
-
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
