Search code examples
sqlstringsqlitevarchar

Select between varchar as a date returns null

I have a table attendance_sheet and it has column string_date which is a varchar.

This is inside my table data.

id | string_date  | pname 
1  | '06/03/2013' | 'sam'
2  | '08/23/2013' | 'sd'
3  | '11/26/2013' | 'rt'

I try to query it using this range.

SELECT * FROM attendance_sheet
where string_date between '06/01/2013' and '12/31/2013'

then it returns the data.. but when I try to query it using this

SELECT * FROM attendance_sheet
where string_date between '06/01/2013' and '03/31/2014'

it did not return any results...

It can be fixed without any changing the column type for example the string_date which is a varchar will be changed into a date?

Does anyone has an Idea about my case? any help will be appreciated, thanks in advance ..

Solution

  • Use strftime

    SELECT * FROM attendance_sheet
where strftime(string_date,'%m/%d/%Y') between '2013-06-01' and '2013-31-21'