Sqlite convert string to date

Question

I have date stored as string in an sqlite database like  28 11 2010   I want to convert the string to date     Specifically I have to convert lots of string dates between two dates   In postgresql  I use to date  30 11 2010   dd MM yyyy    how can I do the same thing with sqlite   Something like this   SELECT   FROM table     WHERE   to date column  dd MM yyyy       BETWEEN to date  01 11 2010   dd MM yyyy       AND     to date  30 11 2010   dd MM yyyy

User · Answer

This is for fecha TEXT  format date YYYY-MM-dd HH mm ss for instance I want all the records of Ene-05-2014  2014-01-05    SELECT   fecha  FROM   Mytable  WHERE   DATE substr fecha  1 4    substr fecha  6 2   substr fecha  9 2   BETWEEN   DATE 20140105   AND   DATE 20140105

User · Answer

Saved date as TEXT  20 10 2013 03 26   To do query and to select records between dates   Better version is   SELECT TIMSTARTTIMEDATE  FROM TIMER  WHERE DATE substr TIMSTARTTIMEDATE 7 4    substr TIMSTARTTIMEDATE 4 2    substr TIMSTARTTIMEDATE 1 2    BETWEEN DATE 20131020  AND DATE 20131021     the substr from 20 10 2013 gives 20131020 date format DATE 20131021  - that makes SQL working with dates and using date and time functions   OR  SELECT TIMSTARTTIMEDATE  FROM TIMER  WHERE DATE substr TIMSTARTTIMEDATE 7 4     -    substr TIMSTARTTIMEDATE 4 2     -    substr TIMSTARTTIMEDATE 1 2    BETWEEN DATE  2013-10-20   AND DATE  2013-10-21      and here is in one line  SELECT TIMSTARTTIMEDATE FROM TIMER WHERE DATE substr TIMSTARTTIMEDATE 7 4    -   substr TIMSTARTTIMEDATE 4 2    -   substr TIMSTARTTIMEDATE 1 2   BETWEEN DATE  2013-10-20   AND DATE  2013-10-21

User · Answer

I have a database where dates are stored in d F Y format  20 Nov 2017  and to convert it to a machine readable date Y-m-d  I use this method to update the entire table to a proper format   If you only want the date formatting look at the inner select how I formatted the date   update TABLENAME as realTABLE set created at     select      -- Get Year     substr tmpTABLE created at  -4  4           -          -- Get Month       substr             replace  replace  replace  replace  replace  replace  replace  replace  replace  replace  replace  replace  tmpReis szAanmaakDatum                Dec     -12-       Nov     -11-       Oct     -10-       Sep     -09-       Aug     -08-       Jul     -07-       Jun     -06-       May     -05-       Apr     -04-       Mar     -03-       Feb     -02-       Jan     -01-              -- Get it from the original space location   1  then get the two numbers              instr tmpTABLE created at       1  2           -          -- Get day  prepend with a zero if there s a zero lacking       substr  00     tmpTABLE created at  -2  2  as foo from TABLENAME as tmpTABLE  where tmpTABLE id   realTABLE id     -- Check for valid matching formats  don t do those that already were converted    where created at like               or created at like

User · Answer

The UDF approach is my preference compared to brittle substr values      usr bin env python3 import sqlite3 from dateutil import parser from pprint import pprint   def date parse s           Converts a string to a date         try          t   parser parse s  parser parserinfo dayfirst True           return t strftime   Y- m- d       except          return None   def dict factory cursor  row           Helper for dict row results         d          for idx  col in enumerate cursor description           d col 0     row idx      return d   def main            Demonstrate UDF         with sqlite3 connect   memory    as conn          conn row factory   dict factory         setup conn                                                                        This is the code that matters  The rest is setup noise          conn create function  date parse   1  date parse          cur   conn cursor           cur execute     select  date   date parse  date   as parsed from  test order by 2               pprint cur fetchall                                                                def setup conn           Setup some values to parse         cur   conn cursor          Make a table     sql           create table  test            id  integer primary key           date  text                    cur execute sql         Fill the table     dates              2 1 03    03 2 04    4 03 05    05 04 06            6 5 2007    07 6 2008    8 07 2009    09 08 2010            2-1-03    03-2-04    4-03-05    05-04-06            6-5-2007    07-6-2008    8-07-2009    09-08-2010            31 12 20    31-12-2020            BOMB              params     x   for x in dates      cur executemany     insert into  test   date   values          params    if   name         main         main     This will give you these results      date    BOMB     parsed   None      date    2 1 03    parsed    2003-01-02       date    2-1-03    parsed    2003-01-02       date    03 2 04    parsed    2004-02-03       date    03-2-04    parsed    2004-02-03       date    4 03 05    parsed    2005-03-04       date    4-03-05    parsed    2005-03-04       date    05 04 06    parsed    2006-04-05       date    05-04-06    parsed    2006-04-05       date    6 5 2007    parsed    2007-05-06       date    6-5-2007    parsed    2007-05-06       date    07 6 2008    parsed    2008-06-07       date    07-6-2008    parsed    2008-06-07       date    8 07 2009    parsed    2009-07-08       date    8-07-2009    parsed    2009-07-08       date    09 08 2010    parsed    2010-08-09       date    09-08-2010    parsed    2010-08-09       date    31 12 20    parsed    2020-12-31       date    31-12-2020    parsed    2020-12-31      The SQLite equivalent of anything this robust is a tangled weave of substr and instr calls that you should avoid

User · Answer

As Sqlite doesn t have a date type you will need to do string comparison to achieve this  For that to work you need to reverse the order - eg from dd MM yyyy to yyyyMMdd  using something like   where substr column 7   substr column 4 2   substr column 1 2         between  20101101  and  20101130

User · Answer

One thing you should look into is the SQLite date and time functions  especially if you re going to have to manipulate a lot of dates  It s the sane way to use dates  at the cost of changing the internal format  has to be ISO  i e  yyyy-MM-dd

User · Answer

If Source Date format isn t consistent there is some problem with substr function  e g    1 1 2017 or 1 11 2017  or  11 11 2017  or  1 1 17 etc    So I followed a different apporach using a temporary table  This snippet outputs  YYYY-MM-DD    time if exists   Note that this version accepts Day Month Year format  If you want Month Day Year swap the first two variables DayPart and MonthPart  Also  two year dates  44- 99 assumes 1944-1999 whereas  00- 43 assumes 2000-2043    BEGIN       CREATE TEMP TABLE  DateconvertionTable   Id TEXT PRIMARY KEY  OriginalDate  TEXT    SepA  INTEGER   DayPart TEXT Rest1 TEXT  SepB  INTEGER   MonthPart TEXT  Rest2 TEXT  SepC  INTEGER   YearPart TEXT  Rest3 TEXT  NewDate TEXT       INSERT INTO  DateconvertionTable   Id OriginalDate   SELECT SourceIdColumn  SourceDateColumn From   SourceTable        --day Part  If day is first       UPDATE  DateconvertionTable  SET SepA instr OriginalDate            UPDATE  DateconvertionTable  SET DayPart substr OriginalDate 1 SepA-1        UPDATE  DateconvertionTable  SET Rest1 substr OriginalDate SepA 1        --Month Part  If Month is second       UPDATE  DateconvertionTable  SET SepB instr Rest1           UPDATE  DateconvertionTable   SET MonthPart substr Rest1  1 SepB-1       UPDATE  DateconvertionTable  SET Rest2 substr Rest1 SepB 1        --Year Part  3d       UPDATE  DateconvertionTable  SET SepC instr Rest2                   --Use Cases In case of time string included     UPDATE  DateconvertionTable   SET YearPart  CASE WHEN SepC 0 THEN Rest2 ELSE substr Rest2 1 SepC-1   END              --The Rest considered time     UPDATE  DateconvertionTable   SET Rest3  CASE WHEN SepC 0 THEN     ELSE substr Rest2 SepC 1  END              -- Convert 1 digit day and month to 2 digit     UPDATE  DateconvertionTable  SET DayPart 0  DayPart WHERE CAST DayPart AS INTEGER  lt 10      UPDATE  DateconvertionTable  SET MonthPart 0  MonthPart WHERE CAST MonthPart AS INTEGER  lt 10              --If there is a need to convert 2 digit year to 4 digit year  make some assumptions        UPDATE  DateconvertionTable  SET YearPart 19  YearPart WHERE CAST YearPart AS INTEGER  gt  44 AND CAST YearPart AS INTEGER  lt 100      UPDATE  DateconvertionTable  SET YearPart 20  YearPart WHERE CAST YearPart AS INTEGER  lt 44 AND CAST YearPart AS INTEGER  lt 100       UPDATE  DateconvertionTable  SET NewDate   YearPart      -     MonthPart     -     DayPart           Rest3         UPDATE  SourceTable  SET SourceDateColumn  Select NewDate FROM DateconvertionTable WHERE  DateconvertionTable  id SourceIdColumn       END

User · Answer

I am storing the date as  DD-MON-YYYY format  10-Jun-2016  and below query works for me to search records between 2 dates   select date  substr date 8 11      -     substr date 4 4     substr date  1 2    case  substr date  4 3   when  Jan  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Jan     01     when  Feb  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Feb     02     when  Mar  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Mar     03     when  Apr  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Apr     04     when  May  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    May     05     when  Jun  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Jun     06     when  Jul  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Jul     07     when  Aug  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Aug     08     when  Sep  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Sep     09     when  Oct  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Oct     10     when  Nov  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Nov     11     when  Dec  then strftime   s   replace substr date 8 11      -     substr date 4 4     substr date  1 2    Dec     12     else  0  end as srcDate from payment where srcDate  gt   strftime   s    2016-07-06   and srcDate  lt   strftime   s    2016-09-06

User · Answer

convert a string into date little issue think with indexing mmm 3 3 but works added a month on to the date string SELECT substr  12Jan20  1 2  as dday  date substr  12Jan20  6 7     00-     case substr  12Jan20  3 3  when  Jan  then  01  when  Feb  then  02  when  Mar  then  03  when  Apr  then  04  when  May  then  05   when  Jun  then  06  when  Jul  then  07  when  Aug  then  08  when  Sep  then  09  when  Oct  then  10  when  Nov  then  11  when  Dec  then  12  end      -   substr  12Jan20  1 2     1 month   as tt

[string] Sqlite convert string to date

Examples related to string

Examples related to sqlite

Examples related to date

Examples related to date-format