SQLite Forum

.param converts text to number - How to avoid this
Login
It seems that the SQLite CLI is evaluating your date as the expression `2020 minus 10 minus 1 = 2009`. Placing parentheses around the date seems to do the trick in preserving the value.

Reviewing the docs, it's not clear to me when or how `.param set` evaluates expressions. Here are some examples of the current behaviour:

```
SQLite version 3.34.0 2020-11-02 00:40:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .param init
sqlite> .mode table
sqlite> .param set $d '2020-10-01'
sqlite> SELECT * FROM sqlite_parameters;
+-----+-------+
| key | value |
+-----+-------+
| $d  | 2009  |
+-----+-------+
sqlite> .param set $d ('2020-10-01')
sqlite> SELECT * FROM sqlite_parameters;
+-----+------------+
| key |   value    |
+-----+------------+
| $d  | 2020-10-01 |
+-----+------------+
sqlite> .param set $expr1 Abs(-10)
sqlite> .param set $expr2 Upper('abc')
sqlite> .param set $expr3 SubStr('abcdef', 1, 3)
.parameter CMD ...       Manage SQL parameter bindings
   clear                   Erase all bindings
   init                    Initialize the TEMP table that holds bindings
   list                    List the current parameter bindings
   set PARAMETER VALUE     Given SQL parameter PARAMETER a value of VALUE
                           PARAMETER should start with one of: $ : @ ?
   unset PARAMETER         Remove PARAMETER from the binding table
sqlite> .param set $expr3 "SubStr('abcdef', 1, 3)"
sqlite> SELECT * FROM sqlite_parameters;
+--------+------------+
|  key   |   value    |
+--------+------------+
| $d     | 2020-10-01 |
| $expr1 | 10         |
| $expr2 | ABC        |
| $expr3 | abc        |
+--------+------------+
```