131

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')
Improve this question
0

6 Answers 6

Reset to default
179

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

How does one order by date with such strings?
When you format dates like this, date ordering and lexical ordering work out the same. E.g. '2008-02-01' > '2007-02-01', '2008-01-02' > '2008-01-01' both as strings and as dates. But you don't strictly need to care about this because SQLite ORDER BY will take care of ordering for you.
Note that you can't reliably compare dates with different precision using lexical ordering, e.g "SELECT '2007-01-02 10:00:00' > '2007-01-02 10:00';" returns 1 but "SELECT datetime('2007-01-02 10:00:00') > datetime('2007-01-02 10:00');" returns 0.
@itowlson How will this work when you have multi lingual application?
Note: The single quotes ' ' are actually needed. No other answer tell you this.
51

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.

Improve this answer

2 Comments

FYI, .xxxxxx here in @TorValamo's syntax = .SSS = %f in the SQLite documentation, i.e. fractional seconds.
DateTime.parse('2016-05-28 14:27:00 -0300').strftime('%Y-%m-%d %H:%M:%S')
18

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

Improve this answer

1 Comment

Although not listed in the table the docs also state that you may add 'Z' or offsets such as '-0400'
15

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

Improve this answer

1 Comment

this is worked, one thing is wrong. CURRENT_TIMESTAMP return datetime without UTC, do you know how to resolve this problem. Thanks in advance!
10

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

Improve this answer

1 Comment

You should consider answer the question, with links that support your answer, not just a bunch of links
0

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.

Improve this answer

1 Comment

Doesn't cut it for dates if e.g. you wanted to sort by that field; you end up with a string comparison...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.