Invalid sqlite date rendering CAST AS DATE, DATEDIFF and DATEADD

[MaximMoinat]

For sqlite, rendering of DATEADD(month,@months,@date) is not valid.
Underlying issue is that for sqllite the plural 'months' is used, where it should be singular 'month'.

SqlRender/inst/csv/replacementPatterns.csv

Line 822 in bd72fb4

sqlite,"DATEADD(months,@months,@date)","CAST(STRFTIME('%s', DATETIME(@date, 'unixepoch', (@months)||' months')) AS REAL)"

[MaximMoinat]

In addition, the following also produce invalid sql:

• CAST('1900-01-01' AS DATE) renders into CAST(STRFTIME('%s', SUBSTR(CAST('1900-01-01' AS TEXT), 1, 4) || '-' || SUBSTR(CAST('1900-01-01' AS TEXT), 5, 2) || '-' || SUBSTR(CAST('1900-01-01' AS TEXT), 7))
  • This assumes 19000101 was used as input date format. The substrings on indexes 5,2 and 7 give '-0' and '1-01' as date parts respectively, which produces an malformed date string when concatenated again.
  • Fixed by using '19000101' as input date format. Is this preferred date format documented somewhere?
• DATEDIFF(d, start, end) renders into JULIANDAY(end, 'unixepoch') - JULIANDAY(start, 'unixepoch')
  • This causes issues when further calculation is done with the difference (e.g. multiplied by 2). The replacement should be in between parenthesis (JULIANDAY(end, 'unixepoch') - JULIANDAY(start, 'unixepoch'))