How to search between two dates in livecode.
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
How to search between two dates in livecode.
Hello everyone,
What are the steps to search two dates in livecode? since livecode doesn't have a calendar picker in the pallete tool. the dates are stored in my database. See below the example picture i got it from the internet.
What are the steps to search two dates in livecode? since livecode doesn't have a calendar picker in the pallete tool. the dates are stored in my database. See below the example picture i got it from the internet.
Thank you & God Bless Everyone
Regards,
lemodizon
Regards,
lemodizon
Re: How to search between two dates in livecode.
If you are using an SQL database... here's the code I use:
you will need to customise code for the database name, path and columns you have... Everything in -- -- will need renaming, including the hyphens (i.e. "--DataGrid Name--" will become "Accounts")
Your biggest hurdle, and the one that drove me insane, is date format. If you are using SQL, store it in YYYY-MM-DD format, not DD/MM/YYYY or any other one. SQL will only recognise the one format, and if it's not in that, it won't search it as a date. When you create your SQL table, you will also need the Date column to be configured as DATE, not anything else. If you want the output that will be shown to be in another date format, there are plenty of ways to reformat the date.
Hope that helps.
XdM
Code: Select all
local sDatabaseID
local sClientDatabaseID
local tSQL
command setDatabaseID pDatabaseID
put pDatabaseID into sDatabaseID
end setDatabaseID
function getDatabaseID
return sDatabaseID
end getDatabaseID
on preOpenCard
local tDatabasePath, tDatabaseID
put "--File path of database\DatabaseName.sqlite--" into tDatabasePath -- just add your database details between the -- --
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
setDatabaseID tDatabaseID
end preOpenCard
Command SearchDates
put getDatabaseID() into tDatabaseID
put Field "Start Date" into tStartDate
put Field "End Date" into tEndDate
put "SELECT * FROM --DataBaseName-- WHERE Date BETWEEN '"&tStartDate&"' AND '"&tEndDate&"';" into tSQL --add your database name between -- --
put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tList
set the dgData of group "--DataGrid Name--" to empty --add datagrid name between -- --
repeat with x=1 to the number of lines of tList
set the itemdelimiter to tab
put item 1 of line x of tList into theDataA["--Column Name--"]
put item 2 of line x of tList into theDataA["--Column Name--"]
put item 3 of line x of tList into theDataA["--Column Name--"]
put item 4 of line x of tList into theDataA["--Column Name--"]
put item 5 of line x of tList into theDataA["--Column Name--"]
put item 6 of line x of tList into theDataA["--Column Name--"]
put item 7 of line x of tList into theDataA["--Column Name--"] --repeat for however many columns you have--
dispatch "AddData" to group "--Datagrid Name--" with theDataA,x
end repeat
end SearchDates
Your biggest hurdle, and the one that drove me insane, is date format. If you are using SQL, store it in YYYY-MM-DD format, not DD/MM/YYYY or any other one. SQL will only recognise the one format, and if it's not in that, it won't search it as a date. When you create your SQL table, you will also need the Date column to be configured as DATE, not anything else. If you want the output that will be shown to be in another date format, there are plenty of ways to reformat the date.
Hope that helps.
XdM
-
- VIP Livecode Opensource Backer
- Posts: 9648
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: How to search between two dates in livecode.
Hi.
Is this a dataBase question or a LiveCode question?
If a LC question, what are you actually asking? What does "search two dates" mean? To identify the time period between two dates? To find all instances of something that fall between those two dates, or on those particular dates?
Can you give a quick example of what you need?
Craig
Is this a dataBase question or a LiveCode question?
If a LC question, what are you actually asking? What does "search two dates" mean? To identify the time period between two dates? To find all instances of something that fall between those two dates, or on those particular dates?
Can you give a quick example of what you need?
Craig
Re: How to search between two dates in livecode.
It's a database question, I am sure.
Xero nailed it.
Xero nailed it.
Re: How to search between two dates in livecode.
Hello Xero,
Yup, I will use sql database for my own practice. Thanks for the steps and advice I’ll try it later. Stay safe
Yup, I will use sql database for my own practice. Thanks for the steps and advice I’ll try it later. Stay safe
Thank you & God Bless Everyone
Regards,
lemodizon
Regards,
lemodizon
Re: How to search between two dates in livecode.
If you're still setting up your SQL database, then there are a few more steps in setting up the database, accessing it, etc. There are some really good tutorials for that stuff on the Livecode page/ forum. The search between dates function, not so much. I muddled around for a while, and now have a very well- functioning search between dates part of my business app. What I posted should get you through that part. Just remember to format the column in the DB, and make sure you search the right date format in the DB. Presenting it to the user in a different format is easily enough done after you have searched.
XdM
XdM
Re: How to search between two dates in livecode.
dunbarx wrote: ↑Fri Nov 13, 2020 2:40 pmHi.
Is this a dataBase question or a LiveCode question?
If a LC question, what are you actually asking? What does "search two dates" mean? To identify the time period between two dates? To find all instances of something that fall between those two dates, or on those particular dates?
Can you give a quick example of what you need?
Craig
Hi dunbarx,
Sorry if I make you confuse in my question above. in my practice, I just want to display all the activities from the past date up to the current date well this just an example that came in my mind. Thanks
Thank you & God Bless Everyone
Regards,
lemodizon
Regards,
lemodizon
Re: How to search between two dates in livecode.
Thanks. noted on this stay safe everyone.Xero wrote: ↑Sat Nov 14, 2020 4:36 amIf you're still setting up your SQL database, then there are a few more steps in setting up the database, accessing it, etc. There are some really good tutorials for that stuff on the Livecode page/ forum. The search between dates function, not so much. I muddled around for a while, and now have a very well- functioning search between dates part of my business app. What I posted should get you through that part. Just remember to format the column in the DB, and make sure you search the right date format in the DB. Presenting it to the user in a different format is easily enough done after you have searched.
XdM
Thank you & God Bless Everyone
Regards,
lemodizon
Regards,
lemodizon