SQLite: Difference between revisions
Jkuehlthau (talk | contribs) No edit summary |
Jkuehlthau (talk | contribs) |
||
Line 2: | Line 2: | ||
Note that when working with "DateTime" fields in SQLite, the value saved in the field and used for comparison operations must match one of the 10 allowed formats: https://www.sqlite.org/lang_datefunc.html. | Note that when working with "DateTime" fields in SQLite, the value saved in the field and used for comparison operations must match one of the 10 allowed formats: https://www.sqlite.org/lang_datefunc.html. | ||
Starfish's FormatDate function can help with this. See this vbscript for an example: | Starfish's FormatDate function can help with this. See this vbscript for an example: | ||
<source lang=" | <source lang="vb"> | ||
Sub VBScriptProcedure | Sub VBScriptProcedure | ||
'LogMessage FormatDate(Now, "yyyy-MM-dd HH:mm:ss") | 'LogMessage FormatDate(Now, "yyyy-MM-dd HH:mm:ss") | ||
Line 14: | Line 14: | ||
End Sub | End Sub | ||
</source> | </source> | ||
=Levenshtein= | =Levenshtein= |
Latest revision as of 20:34, 1 April 2019
DateTime Fields
Note that when working with "DateTime" fields in SQLite, the value saved in the field and used for comparison operations must match one of the 10 allowed formats: https://www.sqlite.org/lang_datefunc.html. Starfish's FormatDate function can help with this. See this vbscript for an example:
Sub VBScriptProcedure 'LogMessage FormatDate(Now, "yyyy-MM-dd HH:mm:ss") 'LogMessage FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss") If PreviewMode <> True Then dim b b = SQLiteUpdate("contacts_to_update_from_leads","processed='1', date_modified='"&FormatDate(Now, "yyyy-MM-dd HH:mm:ss")&"'","contactid='@@ORG:contactid@@' and leadid='@@ORG:leadid@@' and (processed<>'1' or processed is null)") dim d d = SQLiteDelete("contacts_to_update_from_leads","processed='1' and date_modified<'"&FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")&"'") End If End Sub
Levenshtein
The SQLite connector has the Levenshtein function! A couple of examples:
SELECT LEVENSHTEIN( 'Microsoft', 'Technology Advisors, Inc.' )
Result: 21
SELECT LEVENSHTEIN( 'Technology Advisors Inc', 'Technology Advisors, Inc.' )
Result: 2
Comparing 2 strings is obviously less than useful, so you would want to do something like:
SELECT * FROM TABLE WHERE LEVENSHTEIN('Technology Advisors, Inc.', TABLE.NAME) < 5
Issues
If your source SQLite table has a DateTime field, you may get the error: “String not recognized as a valid datetime”. If you get this error, add "datetimeformat=CurrentCulture", without double quotes, to the "Additional Connection String Parameters:" field on the Origin tab.