SQLite Date and Time Format

SQLite allows small file based SQL like databases to be created quickly on systems without access to SQL Server or MySQL and is especially suited to UWP applications that do not need to be connected to a corporate database and is extremely popular in just about all mobile phone applications that need record based data storage. SQLite however, requires the date and time to be formatted in a particular way before it will accept it in a standard query.

Source Code

There are two operations required, the first is to convert the standard .net DateTime object into a SQLite compatible string so that it can be saved and the second is to convert it back again when a record is reloaded.

The code to convert the standard .net DateTime object into a string is quite straightforward and a method to get the current date and time is often all that is required when creating new database records.

When loading the record back out of the database the reverse operation is required in order to convert the date and time back into a .net DateTime object.

Conclusion

Initially it appears that with a SQLite declared type of ‘DATETIME’ in the ‘CREATE TABLE’ script that a direct cast into the .net DateTime object should be possible. However, like many other SQLite data types (at the time of writing) the results need to be in a string format to work correctly when binding them to a statement.

About Open Technologies Limited

OTL specialise in the design and development of real-time software and hardware applications. We strive to innovate and bring our customers both cutting edge technologies and efficient solutions to their problems. With many years of experience working in industrial and consumer sectors we are always conscious of the need for reliable, maintainable and sustainable products. We are always looking to work with new clients and welcome the opportunity to discuss how we may be able to help you.

Contact OTL