Sqlite date issue

sqlite

I currently have a CSV file uploaded into a sqlite 3.9.0 database with a column as Period that contains date in M/D/YYYY format as TEXT. I want to do some conditional operations on the database based on dates. I am unable to recognize them as dates. How do I recognize them as dates and do the operations?

Best Answer

The date values must be in one of the supported date formats before you can do most operations on them.

You should configure whatever program that generates the CSV file to output the dates in a proper format. Alternatively, write a script to convert the values. If neither is possible, you could convert them with SQL statements, but SQLite does not have many string functions, so this would be not as easy.

Related Solutions

Store a small log file as a blob in sqlite

For me your last line contradicts the subject (blob vs text), so probably I mistook your idea. But what about making your log file an INSERT statement on the fly? Then feed that into your desired sqlite database. I mean something like this:

$ cat test.sql
INSERT INTO test
SELECT 'something
anything';

$ sqlite3 test < test.sql

$ sqlite3 test
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM test;
something
anything
sqlite>

SQLite CSV Import – Table Creation Issues

The documentation page you linked to is for the current version (3.8.9) of SQLite.

You could dig up the old version of cli.html for 3.7.13, but at that time, .import was pretty much undocumented.

Best Answer

Related Solutions

Store a small log file as a blob in sqlite

SQLite CSV Import – Table Creation Issues

Related Question