CODEWITHSUNDEEP
csqlite
Prakash
Prakash

Reputation: 273

date between query issue in sqlite3

In my data base , i was stored one student data in the following date format is 07-03-2013(dd-mm-yyyy). For example the start date greater than the end date ,no row return in the results.

For Ex.

select student_name from general_details where join_date between '11-02-2013' and '08-03-2013'

it returns 0

if start date less than the end date

select student_name from general_details where join_date between '01-02-2013' and '08-03-2013'

it returns row value 1;

Please suggest ,thanks in advance

Upvotes: 0

Views: 31

Answers (1)

laalto
laalto

Reputation: 152817

The dates are compared alphabetically and not as datetime stamps.

If you can influence how the dates are stored, use a format such as ISO-8601 yyyy-MM-dd or unix epoc timestamps to make the comparisons work.

If you cannot influence the data, you can convert the values in SQL by picking up the components with substr() and concatenating together with ||, like this:

substr(join_date,7,4) || substr(join_date,4,2) || substr(join_date,1,2)

Upvotes: 1

Related Questions