How do I insert datetime value into a SQLite database?

Question

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')

score 174 · Accepted Answer · answered Dec 19 '09 at 18:23

174

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.

answered Dec 19 '09 at 18:23

itowlson

73,686
17
161
157

How does one order by date with such strings? – IgorGanapolsky Jul 30 '13 at 16:28
3

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. – itowlson Jul 31 '13 at 03:59
7

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. – Shane Mar 21 '14 at 17:17
@itowlson How will this work when you have multi lingual application? – Emil Jun 29 '17 at 23:26

score 50 · Answer 2 · edited Dec 19 '09 at 22:59

50

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.

edited Dec 19 '09 at 22:59

Peter Mortensen

30,738
21
105
131

answered Dec 19 '09 at 18:24

Tor Valamo

33,261
11
73
81

3

FYI, `.xxxxxx` here in @TorValamo's syntax = `.SSS` = %f in the SQLite documentation, i.e. fractional seconds. – Flak DiNenno Feb 25 '13 at 01:12
2

DateTime.parse('2016-05-28 14:27:00 -0300').strftime('%Y-%m-%d %H:%M:%S') – rodvlopes May 28 '16 at 17:36

score 18 · Answer 3 · answered Dec 19 '09 at 18:28

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:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now

Reference: SQLite Date & Time functions

Although not listed in the table the docs also state that you may add 'Z' or offsets such as '-0400' — coolaj86, Jul 02 '14 at 05:59

score 15 · Answer 4 · edited Aug 28 '15 at 16:59

15

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

edited Aug 28 '15 at 16:59

Alexander Farber

21,519
75
241
416

answered Oct 15 '13 at 15:53

freezing_

984
1
9
13

2

this is worked, one thing is wrong. CURRENT_TIMESTAMP return datetime without UTC, do you know how to resolve this problem. Thanks in advance! – Ulug'bek May 04 '17 at 15:04

score 9 · Answer 5 · edited Sep 16 '12 at 20:51

9

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

edited Sep 16 '12 at 20:51

Gonzalo.-

12,512
5
50
82

answered Sep 14 '12 at 11:39

Ali Amanzadegan

174
1
5

5

You should consider answer the question, with links that support your answer, not just a bunch of links – Gonzalo.- Sep 14 '12 at 19:16

score 0 · Answer 6 · edited Dec 19 '09 at 22:57

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.

edited Dec 19 '09 at 22:57

Peter Mortensen

30,738
21
105
131

answered Dec 19 '09 at 19:29

Messy

1,427
1
9
6

Doesn't cut it for dates if e.g. you wanted to sort by that field; you end up with a string comparison... – G__ Jul 05 '11 at 02:02

How do I insert datetime value into a SQLite database?

6 Answers6

Linked

Related