select entries between two dates in sql database

[Dodo_Hunziker]

Thanks Thim for your patience with me.

Trying this line doesn’t work:

dim fromDate as new date (2007, 4, 15)
"select * from accounting where SUBSTR(date,7,4) || '-' || SUBSTR(date,4,2) || '-' || SUBSTR(date,1,2) as cvDate > '"+fromDate.SQLDate+"' as cvDate"

btw, this one works:

"select * from accounting where julianday(SUBSTR(date,7,4) || '-' || SUBSTR(date,4,2) || '-' || SUBSTR(date,1,2)) > julianday('2007-01-01')"

[Tim_Hare]

@249437:@Dodo Hunziker

‘“+fromDate.SQLDate+”’ as cvDate"

Drop the “as cvDate” part of that line.

[olivierV]

Dodo, do not go too fast. Take the time to understand the functioning of dates in Sqlite, you will not regret it: http://www.sqlite.org/lang_datefunc.html

You can do tests with a tool like http://sqlitebrowser.org

julianDay is mainly useful to calculate the number of days between two dates. In your request, it is not necessary.

But frankly, it does not seem a good idea to use SUBSTR everytime in your requests. It is better to change all your dates in the database once and for all. It is during the display of the date that you adapt to the user.

[Dodo_Hunziker]

Thanks everyone. I know, in the future I will convert the dates on entry to the database for easier use later on.

[Norman_P]

@249399:@Maximilian Tyrtania

But that shouldn’t be an excuse to import them that way. Your import routine should make an effort to convert whatever you throw at it into an SQL friendly date representation. Tim is dead on - dates should be dates in databases. Else you lose the possibility to do any meaningful operation on that field (search, order by).

Biggest issue is depending on how excel is set up it might be mm/dd/yyyy, dd/mm/yyyy or any one of many other possible formats
If this is imported from an Excel spreadsheet that is exported as csv or tab delimited it has no indication which
Importing will need to ask the user