CODEWITHSUNDEEP
sqlcdatabasesqlite
Whoami
Whoami

Reputation: 14408

date handling in sqlite3 confusing.

I am from non database background. I have created a table with one of the field data type TEXT.

dataF TEXT

INSERTION:

Have inserted three records with the values :

'842-2-4'
'842-2-5'
'842-2-6'

SELECTION:

Tring to get the records based on date now.

... where dateF between '842-2-4' and '842-2-10'

It fails.

Whereas, 

... where dateF between '842-2-4' and '842-2-8'

retrieves all the 3 records.

What am i doing wrong ? Why the first statment fails ?

Kindly suggest.

Upvotes: 3

Views: 116

Answers (2)

MatBailie
MatBailie

Reputation: 86715

When comparing strings, the values are compared left to right...

As one string is shorter that the other, you are kind of comparing this...

'842-2-4'
'842-2-1'

Well, nothing is >= '842-2-4' AND <= '842-2-1'.

  • Because '842-2-1' comes before '842-2-4'.

  • And, so, '842-2-10' comes before '842-2-4' too.

  • Just as 'Squiggled' comes before 'Squiggly'

  • And as 'xxx-y-az' comes before 'xxx-y-z'


To compare as you desire, make sure all your dates are padded with 0's.

BETWEEN '0842-02-04' AND '0842-02-10'

But that will only work after you have padded out the values in your table too.


EDIT:

Also, note that this assumes that your format is YYYY-MM-DD. As a string is compared left to right, you must have the highest magnitude values to the left.

(This means that you can't use YYYY-DD-MM and still have native string comparisons behave as you would want them.)

Upvotes: 1

HackyStack
HackyStack

Reputation: 5157

Because you are comparing strings not dates. The computer has no idea these are dates. You have to either store as date and do a date comparison or implement your own logic to analyze strings.

Simply put, it is looking at the 1 in 10 as being less than your values rather than 10 being more. It's string comparison, not date.

Although sqlite doesn't support date types, it does have functions for dealing with them. See here:

http://www.sqlite.org/lang_datefunc.html

Upvotes: 3

Related Questions