Reading DateTime In Select Statements
You need to specify you want the value returning in a DateTime format or you can just get the year portion returned!
Command1->CommandText = "SELECT datetime(LogDateTime) as LogDateTime, SomeOtherColumnName FROM MyTable";
Faster Searches Based On Date
One nice and simple solution is to store dates as an intiger using this format:
YYYYMMDD, so 1st Jan 2015 would be 20150101
Then when performing select queries SQLite only has to work with integers not strings.
Convert C++ DateTime to String for SQLite
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of 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.
String ^DateTimeString = LogDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
Default to now
CREATE TABLE IF NOT EXISTS tbl1(id int primary key, dt datetime default current_timestamp);
The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses. The default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
If the value is NULL, a string constant or number, it is inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns.
For CURRENT_TIME the format is HH:MM:SS.
For CURRENT_DATE it is YYYY-MM-DD.
For CURRENT_TIMESTAMP it is "YYYY-MM-DD HH:MM:SS"