I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error:
<Unable to read data>
The SQL statements are:
create table myTable (name varchar(25), myDate DATETIME)
insert into myTable (name,mydate) Values ('fred','jan 1 2009 13:22:15')
Use CURRENT_TIMESTAMP
when you need it, instead OF NOW()
(which is MySQL)
You have to change the format of the date string you are supplying in order to be able to insert it using the STRFTIME function. Reason being, there is no option for a month abbreviation:
%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %
The alternative is to format the date/time into an already accepted format:
Reference: SQLite Date & Time functions
This may not be the most popular or efficient method, but I tend to forgo strong datatypes in SQLite since they are all essentially dumped in as strings anyway.
I've written a thin C# wrapper around the SQLite library before (when using SQLite with C#, of course) to handle insertions and extractions to and from SQLite as if I were dealing with DateTime objects.
The way to store dates in SQLite is:
yyyy-mm-dd hh:mm:ss.xxxxxx
SQLite also has some date and time functions you can use. See SQL As Understood By SQLite, Date And Time Functions.
Source: Stackoverflow.com