How to search between two dates in livecode.

[lemodizon]

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.

[Xero]

If you are using an SQL database... here's the code I use:

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

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

[dunbarx]

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

[SparkOut]

It's a database question, I am sure.
Xero nailed it.

[lemodizon]

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

[Xero]

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

[lemodizon]

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

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

[lemodizon]

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

Thanks. noted on this stay safe everyone.