Sqlselect query

The following only Code finds the date 2022-03-03 in the database but I know that
there is a date of 2022-03-01 in the database but it is not found.
If I change the Fdate to “2033-02-28” they are boith found…

Anybody any Ideas…?

Fdate = “2022-03-01”
Ldate = “2022-03-31”

Results = db.SelectSQL(“SELECT * FROM Customer WHERE date >= ? AND date <= ?”, Fdate,Ldate)

Ian.

Per the date 2022-02-28 I understand that the date is coded like this YYYY-MM-DD. Is that the way dates are coded for the database you use ?

If this is an SQLite database such a query, using > or < is unlikely to work; SQLite does not have a DATE type.

Giles thanks for the reply.
Yes they are coded as Date and the Fdate and Ldate are Datetime.

And as for what Tim is saying how come I can create a datebase file with Date in it (Sqlite).

If what you say is right then how do you store or search for dates in a sqlite database…?

You can declare a column to be of any type you like, but SQLite only supports a few types. See:

https://www.sqlite.org/datatype3.html

In particular section 2.

I store all dates as REAL - the number of seconds since the unixepoch. That way comparison is easy. The DateTime class can give you that number.

A database is the data storage layer. Presentation of a date/time to the user is the presentation layer of an app and where conversion to a human-readable format (in the user’s preferred format) should be done.

Edit: declaring a column as DATE will give it numeric affinity but dates like 2022-03-01 will be stored as strings. They won’t compare (other than for equality) as you would like.

Thank you it seems I misunderstood what was in the documentation.
Will now have trewrite the whole program to take into account for this.
Most of the routines in my pprogram use the date functtion.

unless one of the other datases use the date …?

Whyever not?
That would imply that IF “Cat” >= “Cat” would be false… surely not?

As strings ‘2022-03-01’ is certainly >= ‘2022-03-01’

Did you mean to say If I change the Fdate to “2033-02-28” ?

Are all the hyphens the same character?
What variable type are FDate and LDate ?

yes i do mean if I change the Fdate to “2022-02-28” it works

If one stores them as SQLDate strings it should. That’s the point of the format… to be chronologically sortable.

-Karen

1 Like

Sorry but am getting lost in this…
Have tried Sqldate but it says that it does not exist.

Not sure how to use unixepoch to convert my input date to real and back again

to be honest am not sure what to do. IF I leave as is then the search works if you input the day before so will see if that will work for it.
Thanks for the info.
Ian

SQLDate is a string the format YYYY-MM-DD

From the docs on DateTime

**SQLDate** As [String](https://documentation.xojo.com/api/data_types/string.html)

Returns the date in SQL date format, *YYYY-MM-DD*.

This property is read-only.

This is an example for a SQL date: 2019-09-03

The following code displays the SQLDate value for the current date.

Var d As DateTime = DateTime.Now Label1.Text = d.SQLDate

---

DateTime.SQLDateTime

**SQLDateTime** As [String](https://documentation.xojo.com/api/data_types/string.html)

Returns the date in SQL date/time format, YYYY-MM-DD HH:MM:SS.

This property is read-only.

This is an example of an SQL date/time: 2019-09-03 13:39:16

The following code displays the current SQL date/time.

Var d As DateTime = DateTime.Now Label1.Text = d.SQLDateTime

Could the issue be in the time portion of the date?

-Karen

Make sure the time portion of fdate is 00:00:00 and the time portion of ldate is 23:59:59.

I am sorry if I did not explain this but here goes.
Am trying to store a date ing the sqlit database.

I then want to search for the date which is between two dates (Fdate and Ldate) both of which are in the format of yyyy-mm-dd as is the date in the datebase.
if I search using 2022-03-01 and 2022-03-31 I get the date of 2022-03-03 but not the date of 2022-03-01

Have just read Tim hares comment so wwill try that.

Ian

Tim
sorry should have said they are not datetime

If somebody could answer me this I would appreciate it.

I have a date as string
I can convert it to datetime but not sure how to save that in database

how can I store it in my database so I can retreive it by searching using two dates.
.
sorry is 1.20am here so off to bed.

save it as a double in the database - datetime.SecondsFrom1970
can do < > operations
can convert back to date
dim d as datetime(double_seconds,TimeZone.Current)

if you try
WHERE date >= Fdate AND date <= Ldate)
that should work

Could not sleep so thought I would have have a go with Daniel’s idea and have got a routine that
converts to double and then converts back.

This allows me to search for the dates between two numbers and includes the search dates.

Thank you for your trouble everybody and have a nice day…

Ian.

1 Like

While that works, you should not need to do that to get it to work.

-Karen

2 Likes