LiveCode Forums

Hey brainstrust...
I'm using an SQL Database with a Column called "Date" that has dates in a format dd/mm/yyyy
I'm trying to do a search for dates between a range and return it to a table.
I just can't get it to work.
I found this code that I have adapted to suit my names: where EPAccounts is the database name, Date is the date column name, 103 is the proper UK date format code, tStartDate and tEndDate is the value of a field with the format dd/mm/yyyy. tSQL is then called in the revDataFromQuery.
This is borrowed from a site where it is supposed to have worked as an SQL query.
When I run it, I get the error:
"revdberr,Database Error: no such column: DATETIME"
What am I doing wrong?
Is there a better way of telling SQL that the dates in the column Date are in the dd/mm/yyyy format and search between the dates?
X

Maybe this helps?
http://www.sqlitetutorial.net/sqlite-date/

Sorry Bogs,
That just runs through standard formatting. I need UK, DD/MM/YYYY formatting, and I need to be able to call it up as that from a database.
Thanks anyway!
X

You could, of course, run every British date through a parser to convert it into an American date . . .
- -
This "may" win me the wooden spoon for innovative programming.

What DB engine are you using?
I am not sure whether this would make a difference to the syntax and whether you could have success with this without any other adjustments, but I wonder whether "Date" is a reserved word and should not be used as a column name? That might be confusing the convert function. On the other hand the syntax doesn't look quite right.
Is the Date column set to contain values formatted as "dd/mm/yyyy"? In which case, are you sure you need to convert at all?
What do you get if you answer tSQL before executing the query? Are the tStartDate and tEndDate substitutions looking correct? Possibly is all you need?
Otherwise maybe using # to wrap the dates instead of single quotes?

Thanks SparkOut,
I don't know what DB engine I am using... I just created an SQLite DB in Livecode... I' not so advanced to know what I am doing! I think I followed a tutorial on the LC site...
These were my first two versions, ' DD/MM/YYYY' first, as it was the most logical. I am aware of the "Don't use Date as a column" theory. I just found out too late. Makes sense. When I run it using that script, I get return data that doesn't act like dates. So, a search between 01/07/2018 and 30/06/2018 will return data right up to 20/08/2019- outside the range. a search of 01/07/2018 to 01/07/2019 returns no data, no error.
This leads me to believe that it's not treating the data as a date, but instead, searching for everything that is between 01 and 30, with any trailing numbers after the first / .
I did not set the column to be formatted as a date. I just did it as char. So...
I have changed the format of the column, and it now returns no data for any search. I inserted a test date in the yyyy-mm-dd format, and it will find that date for a search of yyyy-mm-dd format
Using the #dd/mm/yyyy# format in the code yields this error:
revdberr,Database Error: near "#01": syntax error
I think I am still in the same place, but probably a step further forwards.
It still seems like when I SELECT, it is looking at the Date column and not recognising it as a date. From what I have read, the data needs to be entered as YYYY-MM-DD and then when you output it, you can convert it to DD/MM/YYYY.
I have the problem of having data as DD/MM/YYYY and it just doesn't recognise it as a date...
X