My date variable d1 has just the day, month and year. I want to construct a SQL query which will find all records with that value in calldate, a datetime field.
The following syntax does not work:
var query = "SELECT callsid FROM calls WHERE calldate = '" + d1 + "' ";
Assistance appreciated.
It doesn’t work because of the time portion. Typically there is two ways:
or
select * from table where year(calldate) = year(d1) and month(calldate) = month(d1) and day(calldate) = day(d1)
In your case 1. is more useful, I think.
or you can truncate the time portion from teh datetime field in the database. Depending of the DB in use the syntax could differ, but my experience is that it’s usually: trunc(date)
Paul
I’d prefer to do this search via a SQL Query because I’m going to be including a bunch of other items in the search. They all work, but the date aspect doesn’t. Is there a SQL Query method for searching for just the date portion? There surely must be.
I’ve tried a third way – which doesn’t work. But from my way of looking at it, it really should – but doesn’t. I’m using a calculation called day_calc, have indexed it by creating a datetime field by the same name. This calc zeros out the time to midnight. Day_calc displays the date exactly the same as the variable “d1”. Here’s the routine:
var d1 = new Date(record.calldate);
d1.setHours(0);
d1.setMinutes(0);
d1.setSeconds(0);
d1.setMilliseconds(0);
var maxReturedRows = 10;
var query = "SELECT callsid FROM calls WHERE day_calc = '" + d1 + "' ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,null,maxReturedRows);
controller.loadRecords(dataset);
Returns zero records.
Morely, I have a few questions:
What do you mean by "indexed it… "? Do you mean “stored” it? And how does your calc “zero out the time to midnight”?
The only question is: what is stored and what do you ask for. Right now, you ask a field day_calc to be exactly like a records calldate at 00:00:00.00. If you have that stored, you will find it. Juts use your SQL and query the database directly. We are not talking about a Servoy problem here…
Hi Morley,
Safest way to get a recordset containing one date out of a timestamp column is:
SELECT column FROM table WHERE dateColumn >= servoyDateObject AND dateColumn < servoyDateObject(+1day)
The servoyDateObject can be a date column or javascript variable
With the servoyDateObject you don’t have to worry about the formatting
This should work on all SQL databases .
var d1 = new Date(record.calldate); //create date Object and remove time part
d1.setHours(0);
d1.setMinutes(0);
d1.setSeconds(0);
d1.setMilliseconds(0);
var d2 = new Date(d1) // create second dateObject
d2.setDate(d2.getDate()+1); //add 1 day to your date Object. note: this will also roll from 30 nov to 1 dec
var maxReturedRows = 10;
//in the query, place questionmarks where the dateObjects should come.
// the questionmarks refer to the array (3rd parameter) in “getDataSetByQuery” function
var query = "SELECT callsid FROM calls WHERE day_calc >= ? and day_calc < ?;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,[d1,d2],maxReturedRows);
controller.loadRecords(dataset);
Fabulous, Maarten! Works like a charm.
Your commenting in the code greatly helps understanding of what’s going on here. Very much appreciated.
Now that finding date matches via a SQL Query has become clear, I’d now like to extend the Query to also look for additional factors. I’m searching for records which are near duplicates, consolidating the notes fields of each and then reducing the collection to a single record. Finding identical dates is just part of the search.
I took a guess that I could extend the array and search for “?” in the same style as searching for dates. Doesn’t work. Here’s the code:
var i = 1;
var record = foundset.getRecord(i);
var d1 = new Date(record.calldate); //create date Object and remove time part
d1.setHours(0);
d1.setMinutes(0);
d1.setSeconds(0);
d1.setMilliseconds(0);
var d2 = new Date(d1) // create second dateObject
d2.setDate(d2.getDate()+1); //add 1 day to your date Object. note: this will also roll from 30 nov to 1 dec
var caller = record.callerid;
var peo_id = record.peoid;
var com_id = record.comid;
var maxReturedRows = 10;
//in the query, place questionmarks where the dateObjects should come.
// the questionmarks refer to the array (3rd parameter) in "getDataSetByQuery" function
var query = "SELECT callsid FROM calls WHERE calldate >= ? and calldate < ?";
query += "AND callerid = ? ";
query += "AND peoid = ? ";
query += "AND comid = ? ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,[d1,d2,caller,peo_id,com_id],maxReturedRows);
controller.loadRecords(dataset);
at first glance…
(not sure if that solves everything)
var query = "SELECT callsid FROM calls WHERE calldate >= ? and calldate < ?";
query += "AND callerid = ? ";
query += "AND peoid = ? ";
query += "AND comid = ? ";
will return
"SELECT callsid FROM calls WHERE calldate >= ? and calldate < ?AND callerid = ? "…
(concatenation should have a space between ? and A
Perfect! That word space was all required to make it work. Much thanks, Maarten. The way is cleared to completing this routine.
Morley, I think I gave you this tip before: when you need SQL, you should try to find the data you want by hand using a GUI SQL tool. A tool like that will show you the problems of your query, has a decent help file and so forth. (A problem with SQL is not a problem with Servoy, so you can’t expect Servoy to have great documentation or support on that. )
Once you have a good query, you start to replace parts of the query using your variables. If you have a problem now, you know it is either a mismatch between the content of the variable and the field you query or the string calculation you are doing. If you produce a wrong query, you also see i why in the debugger and the Servoy log file. That error also helps to pin down the problem.
That saves a lot of headache.
Patrick
patrick:
Morley, I think I gave you this tip before: when you need SQL, you should try to find the data you want by hand using a GUI SQL tool. A tool like that will show you the problems of your query, has a decent help file and so forth. (A problem with SQL is not a problem with Servoy, so you can’t expect Servoy to have great documentation or support on that. )Once you have a good query, you start to replace parts of the query using your variables. If you have a problem now, you know it is either a mismatch between the content of the variable and the field you query or the string calculation you are doing. If you produce a wrong query, you also see i why in the debugger and the Servoy log file. That error also helps to pin down the problem.
That saves a lot of headache.
Understood and agreed in principle. However, haven’t found a good GUI SQL tool which I’ve understood. Documentation often skips over the fine points, assuming greater understanding of syntax than is actually the case.
I fully recognize answering questions like this one is beyond Servoy per se. Similarly JavaScript for that matter. I try not to ask until I’ve exhausted other resources, in other words, when I feel really stumped.
In this particular instance, Maarten not only answered the particulars but his comments also opened up my understanding of general principles of SQL Query construction. I suspect this thread may also be useful to other coding newcomers.
I hope, when my questions have become a nuisance, readers of this forum will have the good sense not to respond.
I wasn’t trying to sound complaining, but trying to be helpful. I use MS SQL and find the tools provided sufficient for the job. But you also might want to have a look at Aqua Datastudio (also mentioned in the forum somewhere). That is useful for all kinds of databases.