There is a table in which you need to count the difference of dates, which are given in the format dd.mm.yyyy. But SQL, as far as I understand, can only operate with the yyyy-mm-dd format. What to do?

  • Sorry, are these dates STORED in such a format (in the VARCHAR field, probably), or are they just DISPLAYED in a certain client? - Akina
  • @Akina table is imported from csv, which are given in such a dormate - sm4ll_3gg
  • What difference does it make, from where and in what form is data imported? The main thing - in what format they are stored in the table. Types of fields - what? - Akina
  • @Akina, SQLite lacks full DATETIME support . Those. They are stored in the table as text, even if you specify the type DATE. - MaxU
  • @MaxU And what's the difference, is there a special type or not? TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS") - please have it, and a literal in this format, when used as a date literal (for example, as an argument to the julianday function) will be correctly understood by the server. But the dd.mm.yyyy format is not in the list understood by the server. And if the author used it for data storage - then who is the evil Buratin to him? - Akina

2 answers 2

Something like this:

 sqlite> create table aaa(dt date); sqlite> insert into aaa values('01.01.2017'); sqlite> insert into aaa values('30.01.2017'); sqlite> insert into aaa values('28.02.2017'); sqlite> select * from aaa; dt ---------- 01.01.2017 30.01.2017 28.02.2017 sqlite> select julianday('now') - julianday(substr(dt, 7, 4)||'-'||substr(dt, 4, 2)||'-'||substr(dt, 1, 2)) from aaa; julianday('now') - julianday(substr(dt, 7, 4)||'-'||substr(dt, 4, 2)||'-'||substr(dt, 1, 2)) -------------------------------------------------------------------------------------------- 73.7166386689059 44.7166386689059 15.7166386689059

from the documentation :

SQLite does not have a storing dates and / or times. The SQLite Functions of the Date and Time of Functions are not compatible with TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH: MM: SS.SSS").
  • Since it was noon in Greenwich on November 24, 4714 BC according to the pro leptic calendar Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

It can be used for all applications.

    Use SELECT (JulianDay (eventend) - JulianDay (eventstart))

    • juliandate accepts the yyyy-mm-dd format, as far as I am aware and in any other case returns null - sm4ll_3gg
    • @ sm4ll_3gg yes, it would be wrong, it turns out the only way out is to cut storage - Nick Proskuryakov