How to search between two dates in livecode.

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
lemodizon
Posts: 95
Joined: Thu Apr 05, 2018 3:33 pm

How to search between two dates in livecode.

Post by lemodizon » Fri Nov 13, 2020 4:23 am

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.
Attachments
Two_Dates.PNG
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

Re: How to search between two dates in livecode.

Post by Xero » Fri Nov 13, 2020 2:31 pm

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
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7407
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: How to search between two dates in livecode.

Post by dunbarx » Fri Nov 13, 2020 2:40 pm

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
Posts: 2407
Joined: Sun Sep 23, 2007 4:58 pm

Re: How to search between two dates in livecode.

Post by SparkOut » Fri Nov 13, 2020 6:49 pm

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

lemodizon
Posts: 95
Joined: Thu Apr 05, 2018 3:33 pm

Re: How to search between two dates in livecode.

Post by lemodizon » Sat Nov 14, 2020 3:05 am

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
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

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

Re: How to search between two dates in livecode.

Post by Xero » Sat Nov 14, 2020 4:36 am

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
Posts: 95
Joined: Thu Apr 05, 2018 3:33 pm

Re: How to search between two dates in livecode.

Post by lemodizon » Sat Nov 14, 2020 5:06 am

dunbarx wrote:
Fri Nov 13, 2020 2:40 pm
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
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

lemodizon
Posts: 95
Joined: Thu Apr 05, 2018 3:33 pm

Re: How to search between two dates in livecode.

Post by lemodizon » Sat Nov 14, 2020 5:08 am

Xero wrote:
Sat Nov 14, 2020 4:36 am
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.
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”