How do I insert datetime value into a SQLite database?

SqlDatetimeSqlite

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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 - Sql

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 - Sql

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 - Sql

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

Solution 5 - Sql

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

Solution 6 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Datetime Solutions
Recommended Sqlite Solutions

Previous Solution:

Set value to null in WPF binding

WpfNullableBinding.Net 3.5String

Next Solution:

TSQL - Cast string to integer or return default value

SqlTsql

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionBradView Question on Stackoverflow
Solution 1 - SqlitowlsonView Answer on Stackoverflow
Solution 2 - SqlTor ValamoView Answer on Stackoverflow
Solution 3 - SqlOMG PoniesView Answer on Stackoverflow
Solution 4 - Sqlfreezing_View Answer on Stackoverflow
Solution 5 - SqlAli AmanzadeganView Answer on Stackoverflow
Solution 6 - SqlMessyView Answer on Stackoverflow