Fun with Dates .. not.

Getting Started
1

Hey guys,

I’m using Xojo 2017 R1.1 and writing a Windows Desktop application.
I’m using SQLite.

One of my tables is called ‘ClientSchedule’ and it has a field called ‘Date’ of type Numeric. (this could be the first of my problems?)
My code is as follows:

Using Xojo.Core

Dim sDate As String = StartDate.ToText(Locale.Current, Date.FormatStyles.Short, Date.FormatStyles.None)
Dim eDate As String = EndDate.ToText(Locale.Current, Date.FormatStyles.Short, Date.FormatStyles.None)

Dim sqlAll As String = "SELECT * FROM ClientSchedule WHERE Date >= " + sDate + " AND Date <= " + eDate + “;”

MsgBox(sqlAll)

Dim appointmentsRS As RecordSet
appointmentsRS = DB.SQLSelect(sqlAll)

If DBError Then Return

MsgBox("Records: " + str(appointmentsRS.RecordCount))

I’ve manually (via DB Browser for SQLite) inserted a record:

INSERT INTO ClientSchedule (ClientID, Date, Time, Duration, Cost)
VALUES (“1”, “17-6-27”, “09:00”, “1”, “0”);

When I ‘browse’ the record, the date appears as “27/6/17” (??)

No matter what I’ve done so far, I can’t get my RecordCount to be > 0

??

Any help/suggestions would be greatly appreciated.

Thanks.

2

oh and StartDate and EndDate are passed to my method as Xojo.Core.Date(s)

3

ALWAYS… use SQLDATE or SQLDATETIME when storing dates in a DATABASE (ie. TEXT/STRING datatype)

2017-06-28

This insures that ORDER BY is always correct
This insures that BETWEEN and other comparisons of greater or lesser are correct

Oh… did I say ALWAYS??? if I didn’t I should have :slight_smile:

Your INSERT did not match the required format

INSERT INTO ClientSchedule (ClientID, Date, Time, Duration, Cost)
VALUES ("1", "2017-06-27", "09:00", "1", "0");

your SQL needed single quotes, sDate and eDate are TEXT values as far as SQL is concerned

Dim sqlAll As String = "SELECT * FROM ClientSchedule WHERE Date >= '" + sDate + "' AND Date <=' " + eDate + "';"

otherwise you had “WHERE DATE >=17-6-27” or “WHERE DATE= -16”