UK Format Date Search- SQL

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Xero
Posts: 152
Joined: Sat Jun 23, 2018 2:22 pm

UK Format Date Search- SQL

Post by Xero » Thu Aug 22, 2019 11:29 am

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:

Code: Select all

   put "SELECT * from EPAccounts WHERE CONVERT(DATETIME, Date, 103) BETWEEN '" & tStartDate & "' and '" & tEndDate & "';" into tSQL
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

bogs
Posts: 5435
Joined: Sat Feb 25, 2017 10:45 pm

Re: UK Format Date Search- SQL

Post by bogs » Thu Aug 22, 2019 1:37 pm

Image

Xero
Posts: 152
Joined: Sat Jun 23, 2018 2:22 pm

Re: UK Format Date Search- SQL

Post by Xero » Thu Aug 22, 2019 1:54 pm

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

richmond62
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 9250
Joined: Fri Feb 19, 2010 10:17 am
Location: Bulgaria

Re: UK Format Date Search- SQL

Post by richmond62 » Thu Aug 22, 2019 9:23 pm

You could, of course, run every British date through a parser to convert it into an American date . . .
-
Screenshot 2019-08-22 at 23.22.39.png
-
This "may" win me the wooden spoon for innovative programming. 8)
Attachments
HOT DATE.livecode.zip
Here's the stack.
(1.14 KiB) Downloaded 245 times

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: UK Format Date Search- SQL

Post by SparkOut » Thu Aug 22, 2019 9:54 pm

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

Code: Select all

put "SELECT * from EPAccounts WHERE Date BETWEEN '" & tStartDate & "' AND '" & tEndDate & "';" into tSQL
is all you need?
Otherwise maybe using # to wrap the dates instead of single quotes?

Code: Select all

put "SELECT * from EPAccounts WHERE Date BETWEEN #" & tStartDate & "# AND '# & tEndDate & "#;" into tSQL

Xero
Posts: 152
Joined: Sat Jun 23, 2018 2:22 pm

Re: UK Format Date Search- SQL

Post by Xero » Fri Aug 23, 2019 12:43 am

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

Post Reply

Return to “Databases”