Inserting Dates Using "a Simple Delphi Wrapper For Sqlite3"

I am using delphi 2010, and Tim Anderson's SQLite3 wrapper - http://www.itwriting.com/blog/?page_id=659 - but I am having trouble inserting dates Here is my database creation DB.Ex

Solution 1:

Date and Time Datatype

SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Showing here for example the REAL and INTEGER part.

modified SQLite3 wrapper Testfile: uTestSqlite

sSQL := 'CREATE TABLE testtable ([ID] INTEGER PRIMARY KEY,[OtherID] INTEGER NULL,';
sSQL := sSQL + '[Name] VARCHAR (255),[Number] FLOAT,[Date] INTEGER, [notes] BLOB,
       [picture] BLOB COLLATE NOCASE);';
sldb.execsql(sSQL);
sldb.execsql('CREATE INDEX TestTableName ON [testtable]([Name]);');

//begin a transaction
sldb.BeginTransaction;

sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Date) VALUES ("Some Name", 4,
         julianday("now"), strftime("%s","now"));';
sldb.ExecSQL(sSQL);

sSQL := 'INSERT INTO testtable(Name,OtherID,Number,Date,Notes) VALUES ("Another Name",12,
         julianday("2013-03-01"),strftime("%s","2013-03-01"), "More notes");';
sldb.ExecSQL(sSQL);

//end the transaction
sldb.Commit;

[...]

//query the data
sltb := slDb.GetTable('SELECT * FROM testtable');
if sltb.Count > 0 then
begin
//display first row
updateFields;
end;

Show the values:

procedure TForm1.updateFields;
var
Notes: string;
myDate :TDateTime;

begin
ebName.Text := sltb.FieldAsString(sltb.FieldIndex['Name']);
ebID.Text := inttostr(sltb.FieldAsInteger(sltb.FieldIndex['ID']));

if TryJulianDateToDateTime(sltb.FieldAsDouble(sltb.FieldIndex['Number']),myDate)
   then
   ebNumber.Text := DateTimeToStr(myDate)
   else
   ShowMessage('Not a valid Julian date');

myDate:=UnixToDateTime(sltb.FieldAsInteger(sltb.FieldIndex['Date']));
ebDate.Text := DateTimeToStr(myDate);

[...]
end;

Output:

In your problem with DB.ExecSql('CREATE TABLE Tags (..., Born Date NULL,...);');

replace in DB.ExecSql('Insert into Tags (....) VALUES (...

+ quotedStr(frmTag.edtBorn.Text) 

with

'strftime("%Y-%m-%d","'+frmTag.edtBorn.Text+'")'

The value of frmTag.edtBorn.Text must be like 1975-10-21

You can get it with:

ebDate.Text := sltb.FieldAsString(sltb.FieldIndex['Born']);

Solution 2:

I've made my own 'really light' SQLite3 wrapper, but use Variants to decide whether which sqlite3 internal type to use: https://github.com/stijnsanders/TSQLite

There I've found the 'loose typing' sqlite3 internally works really well, and I found out that Delphi's dates stored in a Variant turn into a floating point value in SQLite3 (which TDateTime actually is by the way). A possible downside is the date values are a bit clumsy to manipulate from SQL. I regret to see you've already tried AddParamFloat (and FieldAsFloat) and this didn't appear to work.

So I suggest you store the date as a string, using one if the sqlite date formats, for example using FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',d). See more here: http://www.sqlite.org/lang_datefunc.html

Solution 3:

I use both Delphi 2010 and Tim Anderson's SQLite3 wrapper. Here's what I use to create & use a datetime field. Its fairly straight forward and has been working for me. I trust that you can figure out the concept illustrated below with me having to write you a demo program.

SQL to create field:

  sSQL :=  'CREATE TABLE [someTable] (' +
            '  [somefield1] VARCHAR(12),' +
            '  [somefield2] VARCHAR(12),' +
            '  [myDateTime] DATETIME );';

SQL to populate field:

 sSQL := 'INSERT INTO someTable(somefield1, somefield2, myDateTime)' + 
         '  VALUES ( "baloney1", "baloney2","' + FloatToStr(Now) + '");';

Example of retrieving data from field:

var
sDBFilePathString: string;
sl3tbl: TSqliteTable;
fsldb : TSQLiteDatabase;
FromdbDTField : TDateTime;

begin
   ...
   ... 
    fsldb := TSQLiteDatabase.Create(sDBFilePathString); 
    sl3tbl := fsldb.GetTable('SELECT * FROM someTable');
    FromdbDateTime := StrToFloat(sl3tbl.FieldAsString(sl3tbl.FieldIndex['myDateTime']));
    Showmessage('DT: ' + DateTimeToStr(FromdbDTField));
end;

Result:

 **DT: 10/10/20131:09:53 AM**

I leave it to you to make things prettier or more elegant.