Using a text field with date formated content in a WHERE clause of a SELECT statement
Posted: Sat Nov 14, 2020 1:59 am
Hi everyone,
This is sort of related to the topic submitted by lemodizon's
"How to search between two dates in LiveCode" on Nov. 12,2020.
I'm trying to create the SQLite SELECT statement below, to include
a text field with date formatted content in the WHERE clause.
Using the suggestions by Xero, even though SQLite evidently
doesn't have have date type fields, I created the functions below,
using other's contributions, to increment the gamedate field in
my app and then convert the date format from MM/DD/YYYY to YYYY-MM-DD.
I used these functions to recreate my softball Schedule table with the new
date format and then tried to create my tRecords list in the answer
command below from the new table. No luck using either date format.
It does work if you remove the line referencing the date as noted below.
I love this forum and have gotten so much great help from it.
I've spent hours trying to resolve this one ... any suggestions?
Thanks again,
Lonnie
---------- My Code ------------
put "SELECT * FROM Schedule" && \
"WHERE orgs_id='" & gOrgs_id & "'" && \
"AND eventno='" & gEventNo & "'" && \
"AND gametype='" & gITrnGameType & "'" && \
"AND gamedate='" & gtGameDayDate & "'" && \ -- REMOVE THIS LINE AND IT WORKS
"ORDER BY orgs_id, events_id, setno, gametype, gameno" & ";" into tSQL
---------------------------------------------------------
put revDataFromQuery(comma,return,gConID,tSQL) into tRecords
answer "Display List of Schedule tRecords = " & cr & tRecords with "OK"
--- Excuse the several "answer" commands I throw in for testing
function IncrementDate pDate
-- answer "In IncrementDate with " & pDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
# year, month number, day of the month, hour in 24-hour time,
# minute, second, numeric day of the week
# 2019,9,19,0,0,0,5 ... Sept 19, 2019 after converted to dateitems
put pDate into tDate
convert tDate to dateItems
-- answer "tDate converted to dateItems format = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
# Increment Game Date 1 Day
add 1 to the item 3 of tDate
# Convert tDate back to date format
convert tDate to date
-- answer "tDate converted back to date format = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
# Preceed single digit month and day values with 0's
set the itemDelimiter to "/"
# Convert single value month date to 2 digit
if the number of chars of item 1 of tdate = 1 then
put 0 before item 1 of tdate
end if
# Convert single value day date to 2 digit
if the number of chars of item 2 of tdate = 1 then
put 0 before item 2 of tdate
end if
put item 1 of tdate into tMonth
put item 2 of tdate into tDay
put the internet date into tInternetDate
convert tInternetDate from internet date to dateItems
set itemdelimiter to ","
put item 1 of tInternetDate into tYear
put tMonth & "/" & tDay & "/" & tYear into tDate
--answer "New pDate = " & pDate
return tDate
end IncrementDate
function MySQLDate pDate
# CONVERT A DATE IN THE FORMAT MM/DD/YYYY TO YYYY-MM-DD
put pDate into tDate
-- answer "Date passed to MySQLDate = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
set the itemDelimiter to "/"
put item 1 of tdate into tMonth
put item 2 of tdate into tDay
put item 3 of tdate into tYear
put tYear & "-" & tMonth & "-" & tDay into tDate
return tDate
end MySQLDate
function IncrementTime pTime -- NOT RELATED BUT MIGHT BE HANDY SOMETIME
global giSetsTimeAllowed
put pTime into tTime
convert tTime to short time
convert tTime to dateItems
put item 5 of tTime + giSetsTimeAllowed into item 5 of tTime
convert tTime to short time
return tTime
end IncrementTime
This is sort of related to the topic submitted by lemodizon's
"How to search between two dates in LiveCode" on Nov. 12,2020.
I'm trying to create the SQLite SELECT statement below, to include
a text field with date formatted content in the WHERE clause.
Using the suggestions by Xero, even though SQLite evidently
doesn't have have date type fields, I created the functions below,
using other's contributions, to increment the gamedate field in
my app and then convert the date format from MM/DD/YYYY to YYYY-MM-DD.
I used these functions to recreate my softball Schedule table with the new
date format and then tried to create my tRecords list in the answer
command below from the new table. No luck using either date format.
It does work if you remove the line referencing the date as noted below.
I love this forum and have gotten so much great help from it.
I've spent hours trying to resolve this one ... any suggestions?
Thanks again,
Lonnie
---------- My Code ------------
put "SELECT * FROM Schedule" && \
"WHERE orgs_id='" & gOrgs_id & "'" && \
"AND eventno='" & gEventNo & "'" && \
"AND gametype='" & gITrnGameType & "'" && \
"AND gamedate='" & gtGameDayDate & "'" && \ -- REMOVE THIS LINE AND IT WORKS
"ORDER BY orgs_id, events_id, setno, gametype, gameno" & ";" into tSQL
---------------------------------------------------------
put revDataFromQuery(comma,return,gConID,tSQL) into tRecords
answer "Display List of Schedule tRecords = " & cr & tRecords with "OK"
--- Excuse the several "answer" commands I throw in for testing
function IncrementDate pDate
-- answer "In IncrementDate with " & pDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
# year, month number, day of the month, hour in 24-hour time,
# minute, second, numeric day of the week
# 2019,9,19,0,0,0,5 ... Sept 19, 2019 after converted to dateitems
put pDate into tDate
convert tDate to dateItems
-- answer "tDate converted to dateItems format = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
# Increment Game Date 1 Day
add 1 to the item 3 of tDate
# Convert tDate back to date format
convert tDate to date
-- answer "tDate converted back to date format = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
# Preceed single digit month and day values with 0's
set the itemDelimiter to "/"
# Convert single value month date to 2 digit
if the number of chars of item 1 of tdate = 1 then
put 0 before item 1 of tdate
end if
# Convert single value day date to 2 digit
if the number of chars of item 2 of tdate = 1 then
put 0 before item 2 of tdate
end if
put item 1 of tdate into tMonth
put item 2 of tdate into tDay
put the internet date into tInternetDate
convert tInternetDate from internet date to dateItems
set itemdelimiter to ","
put item 1 of tInternetDate into tYear
put tMonth & "/" & tDay & "/" & tYear into tDate
--answer "New pDate = " & pDate
return tDate
end IncrementDate
function MySQLDate pDate
# CONVERT A DATE IN THE FORMAT MM/DD/YYYY TO YYYY-MM-DD
put pDate into tDate
-- answer "Date passed to MySQLDate = " & tDate with "Cancel" or "OK"
-- if it is "Cancel" then
-- exit to top
-- end if
set the itemDelimiter to "/"
put item 1 of tdate into tMonth
put item 2 of tdate into tDay
put item 3 of tdate into tYear
put tYear & "-" & tMonth & "-" & tDay into tDate
return tDate
end MySQLDate
function IncrementTime pTime -- NOT RELATED BUT MIGHT BE HANDY SOMETIME
global giSetsTimeAllowed
put pTime into tTime
convert tTime to short time
convert tTime to dateItems
put item 5 of tTime + giSetsTimeAllowed into item 5 of tTime
convert tTime to short time
return tTime
end IncrementTime