Sqlite convert string to date

string sqlite date date-format
Claudio picture Claudio · Dec 13, 2010 · Viewed 135.8k times · Source

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

Answer

user533832 picture user533832 · Dec 13, 2010

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'

Related questions

Usage of unicode() and encode() functions in Python

I have a problem with encoding of the path variable and inserting it to the SQLite database. I tried to solve it with encode("utf-8") function which didn't help. Then I used unicode() function which gives me type unicode. print …

Read More
How to concatenate strings with padding in sqlite

I have three columns in an sqlite table: Column1 Column2 Column3 A 1 1 A 1 2 A 12 2 C 13 2 B 11 2 I need to select Column1-Column2-Column3 (e.g. A-01-0001). I want to pad each column with a - I am a beginner …

Read More
String concatenation does not work in SQLite

I am trying to execute a SQlite replace function, but use another field in the function. select locationname + '<p>' from location; In this snip, the result is a list of 0s. I would have expected a …

Read More