Page 1 of 1
Select statements with date parameters
Posted: Thu 11 Jul 2013 14:06
by craigd
Given the following code snippet, I am struggling with how to get only opportunities within a given date range via a parameter. The code below seems to return everything (or at least a lot more than what I was expecting).
I am using 2.3.155.
Code: Select all
command.CommandText = "select * from opportunity where closedate between :start and :end";
command.Parameters.Add("start", SalesforceType.Date);
command.Parameters.Add("end", SalesforceType.Date);
command.Prepare();
command.Parameters["start"].Value = new DateTime(2013, 07, 01);
command.Parameters["end"].Value = new DateTime(2013, 08, 01);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0}\t{1}", reader["CloseDate"], reader["Name"]);
}
}
Re: Select statements with date parameters
Posted: Fri 12 Jul 2013 08:40
by Shalex
We are working on the support of the "between" construction in our SQL'92 Parser. We will post here when it is implemented.
As a workaround, please use the following SELECT statement:
Code: Select all
command.CommandText = "select * from opportunity where closedate >= :start and closedate <= :end";
Re: Select statements with date parameters
Posted: Fri 12 Jul 2013 12:30
by craigd
Thanks. While I was troubleshooting, I also noticed that there are some strange behaviours of the query builder for the dotConnect for Salesforce provider in Visual Studio 2012.
When entering dates as filter parameters, it seems to much around with the date formats. It displays US format in the filter string (m/d/yyy), and it uses localised format in the query string (d/m/yyyy), and yet it was entered using an ISO format (yyyy-mm-dd)
Regards
Re: Select statements with date parameters
Posted: Mon 15 Jul 2013 09:29
by Shalex
craigd wrote:It displays US format in the filter string (m/d/yyy)
This format depends on a default format for dates of your Visual Studio's language.
craigd wrote:it uses localised format in the query string (d/m/yyyy)
You can change the local date formatting from the Windows Control Panel (Start -> Control Panel -> Regional Settings -> Customize -> Short Date Format).
Re: Select statements with date parameters
Posted: Mon 15 Jul 2013 21:40
by craigd
Thanks for your feedback, I have already done that, and the provider is not respecting that format and is using the US format instead.
Re: Select statements with date parameters
Posted: Tue 16 Jul 2013 14:40
by Shalex
We have tested the behaviour with the following steps: created typed DataSet for Opportunity table, opened DataSet Designer, right click on OpportunityTableAdapter > Add Query > go through the wizard to the "Specify a SQL SELECT statement" step > put the query
SELECT *
FROM opportunity
WHERE (closedate > '2013-07-01') AND (closedate <= '2013-08-01')
> pressed the "Query Builder" button. As a result, both query string and filter string in the "Query Builder" window contained dates in the yyyy-mm-dd format.
A localised format used on a test workstation was dd.mm.yyyy.
Could you please specify the exact steps we should follow to reproduce the issue with the m/d/yyy format in the filter string?
Re: Select statements with date parameters
Posted: Thu 25 Jul 2013 10:22
by Shalex
The BETWEEN construction of SQL'92 is supported. We will post here when the corresponding build of dotConnect for Salesforce is available for download.
Re: Select statements with date parameters
Posted: Thu 08 Aug 2013 15:39
by Shalex
New build of dotConnect for Salesforce 2.3.189 is available for download now!
It can be downloaded from
http://www.devart.com/dotconnect/salesf ... nload.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to
http://forums.devart.com/viewtopic.php?f=38&t=27696.