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.

We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.