select entries between two dates in sql database

Databases
1

I would like to get entries from the sql database between two specified dates. I am thinking of something like:

dim fromDate as new Date (2012, 4, 15)
dim toDate as new Date
dim rs as new RecordSet

// connection to database not specially mentioned here

rs = db.SQLSelect("select * from myTable where date > "+fromDate+" and date < "+toDate)

But that obviously doesn’t work. Does someone know how to select the specific db-entries?

2

Hi,

Postgresql
Dim ps As PostgreSQLPreparedStatement = App.DB.Prepare(“select * from myTable where date >= $1 and date <= $2”)
dim rs as recordSet = ps.SQLSelect(fromDate.SQLDate, toDate.SQLDate)

Regards
Mauricio

3

Thanks Mauricio. And RealSQL or SQLlight?

Forgot to mention that the date-field is a string value.

4

Hi,

SQLDate has a YYYY-MM-DD format, if your data is that format should work.

Regards
Mauricio

6 
rs = db.SQLSelect("select * from myTable where date > '"+FROMDATE+"' and date < '"+TODATE+"'")

or

rs = db.SQLSelect("select * from myTable where julianday(date) > julianday("+FROMDATE+") and julianday(date) < julianday("+TODATE+")")

Other way :

rs = db.SQLSelect("select * from myTable where date > date('now', '-1 years', '-90 days') and date < date('now')")

For all lines between 2014-11-26 and today.

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

7

Thanks everyone so far. What if the date is not in the YYYY-MM-DD format? Is there a way to convert it on the fly?

Also: When I use oliviers code, I get an error that the datatype doesn’t match.

8

Addition to post above: The date-string I would like to use looks like this: “01.01.2010”.

9

A) That’s an error in your database. Dates should never be stored that way.
B) You’ll need to reformat the date in order to use it. You can use Substr() to pick out the contents and rearrange them.

10

Alright, thanks Tim. The dates come that way from an import from an accounting software via Excell. Where can I find out more on substr()? The docs don’t deliver any results.

11

That’s an sqlite command. Check their docs: https://www.sqlite.org/lang_corefunc.html

12

You could surely use the ‘BETWEEN’ operator for doing this ?

It exists in SQLite and PostgreSQL.

13

Between isn’t very portable, if that’s a concern.

14

[quote=249279:@Tim Hare]A) That’s an error in your database. Dates should never be stored that way.
B) You’ll need to reformat the date in order to use it. You can use Substr() to pick out the contents and rearrange them.[/quote]

SUBSTR(YourDate,7,4) || ‘-’ || SUBSTR(YourDate,4,2) || ‘-’ || SUBSTR(YourDate,1,2) as cvDate

15

Between is portable among DB’s implementing SQL standard’s (like anything newer than sql 92) - which is the vast majority of them

Whats NOT common is the syntax for the values
Some use #date# - hash marks
Some use ‘date’ - quotes

16

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

17

Thanks Tim and Richard!

18

But I am still doing something wrong as this doesn’t produce any results either:

"select * from myTable where SUBSTR(myDateColumn,7,4) || '-' || SUBSTR(myDateColumn,4,2) || '-' || SUBSTR(myDateColumn,1,2) as cvDate > julianday("+myDate+")")
19

Now you’re comparing apples and oranges. One is a date (presented as a string). The other is a number of days. What are you really trying to do?

20

Oh, I see. I just took the “julianday”-command from olivier without questioning what it does. Trying this command:

dim myDate as string = "2007-01-01"
"select * from accounting where SUBSTR(date,7,4) || '-' || SUBSTR(date,4,2) || '-' || SUBSTR(date,1,2) as cvDate > '"+myDate+"' as cvDate"
21

Use myDate.SQLDate