How do I insert datetime value into a SQLite database?

I am trying to insert a datetime value into a SQLite database. It seems to be sucsessful but when I try to retrieve the value there is an error:

<Unable to read data>

The SQL statements are:

create table myTable (name varchar(25), myDate DATETIME)
insert into myTable (name,mydate) Values ('fred','jan 1 2009 13:22:15')
292082 次浏览
小开
最佳答案

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.

小开

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.

小开

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

小开

This may not be the most popular or efficient method, but I tend to forgo strong datatypes in SQLite since they are all essentially dumped in as strings anyway.

I've written a thin C# wrapper around the SQLite library before (when using SQLite with C#, of course) to handle insertions and extractions to and from SQLite as if I were dealing with DateTime objects.

小开

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

小开

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


提交答案