I wonder whether sqlite3 supports interval function. The following statement is accepted by PostgreSQL, however sqlite3 failed to parse it;
select
...
from
orders
where
...
and o_orderdate < date '1995-03-01' + interval '3' month
Error: near line 4: near "'1995-03-01'": syntax error
Then, I modified the statement a little bit such as;
and o_orderdate < date('1995-03-01') + interval '3' month
This time the error was; Error: near line 4: near "'3'": syntax error
Unfortunately, same trick did not work for the interval function i.e.
and o_orderdate < date('1995-03-01') + interval('3' month)
or
and o_orderdate < date('1995-03-01') + interval('3') month
or even
and o_orderdate < date('1995-03-01') + interval(3 month)
still gave me the syntax error.
Maybe sqlite3 does not support interval function or am I missing something in its usage?
Thanks a lot
The date() function returns the date as text in this format: YYYY-MM-DD. The time() function returns the time as text in this format: HH:MM:SS. The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS.
Using INTEGER to store SQLite date and time values First, create a table that has one column whose data type is INTEGER to store the date and time values. Second, insert the current date and time value into the datetime_int table. Third, query data from the datetime_int table. It's an integer.
Use the STRFTIME() function to format date\time\datetime data in SQLite. This function takes two arguments. The first argument is a format string containing the date/time part pattern. In our example, we use the format string '%d/%m/%Y, %H:%M'.
The SQLite datetime function is a very powerful function that can calculate a date/time value, and return it in the format 'YYYY-MM-DD HH:MM:SS'.
and o_orderdate < date('1995-03-01', '+3 month')
reference of date and time functions in SQLite
try this:
and date(o_orderdate) < date('1995-03-01','+3 month')
In order to calculate dates, you must inform to SQLite that your database field is a date: date(o_orderdate)
SQLite syntax is pretty unflexible, so it's important to make sure that the + sign has no spaces to the following number: '+3 month'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With