Select statements with date parameters

[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"]);
     }
}

[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";

[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

[Shalex]

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.

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).

[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.

[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?

[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.

[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.