Sqlite convert string to date

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

Solution 1:

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'

Solution 2:

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

Solution 3:

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