How do I insert datetime value into a SQLite database?

Solution 1:

The format you need is:

'2007-01-01 10:00:00'

i.e. yyyy-MM-dd HH:mm:ss

If possible, however, use a parameterised query as this frees you from worrying about the formatting details.

Solution 2:

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.

Solution 3:

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:

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now

Reference: SQLite Date & Time functions

Solution 4:

Use CURRENT_TIMESTAMP when you need it, instead OF NOW() (which is MySQL)

Solution 5:

Read This: 1.2 Date and Time Datatype best data type to store date and time is:

TEXT best format is: yyyy-MM-dd HH:mm:ss

Then read this page; this is best explain about date and time in SQLite. I hope this help you