Conversion failed when converting date and/or time from character string

Question

This query is working fine in Sql Server Management Studio, But when I try it to run in a dataset & pass Date(@Param2 & @Pram3) from datetime picker, its throwing an exception Conversion failed. I don't know whats wrong with that, my other queries are working fine.Is this happening because of UNION ALL?

.NET Code

startDatePicker.Format = DateTimePickerFormat.Short;
endDatePicker.Format = DateTimePickerFormat.Short;
DataTable ntwTable = ntw.GetData(owner,start,end);
start = startDatePicker.Value.ToShortDateString();
end = endDatePicker.Value.ToShortDateString();

SQL

SELECT        o.Name, SUM(sq.SQuantity * sq.SRate) AS TotalSale, SUM(sq.PrQuantity * sq.PrRate) AS TotalPurchase, (SUM(sq.PQuantity) - SUM(sq.SQuantity)) 
                                 * (SUM(sq.PQuantity * sq.PRate) / SUM(sq.PQuantity)) AS Inventory
        FROM            (SELECT        OwnerId, CompanyId, Quantity AS PQuantity, RatePerShare AS PRate, 0 AS SQuantity, 0 AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            Purchase
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, 0 AS PQuantity, 0 AS PRate, 0 AS SQuantity, 0 AS SRate, Quantity AS PrQuantity, RatePerShare AS PrRate, Date
                                  FROM            Purchase AS pr
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, Quantity AS PQuantity, 0 AS PRate, 0 AS SQuantity, 0 AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            Bonus
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, Quantity AS PQuantity, CostOfShare AS PRate, 0 AS SQuantity, 0 AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            RightShare
                                  UNION ALL
                                  SELECT        OwnerId, CompanyId, 0 AS PQuantity, 0 AS PRate, Quantity AS SQuantity, RatePerShare AS SRate, 0 AS PrQuantity, 0 AS PrRate, Date
                                  FROM            Sales) AS sq INNER JOIN
                                 Owner AS o ON o.OwnerId = sq.OwnerId
        WHERE        (o.OwnerId = @Param1) AND (sq.Date BETWEEN @Param2 AND @Param3)
        GROUP BY sq.CompanyId, o.Name

Answer 1

(CONVERT(CHAR(10), sq.Date, 111) BETWEEN @Param2 AND @Param3)

Just need to convert.

Answer 2

I'm sure this isn't the best answer, however, it appears this is due to regional settings. If you change the following two lines:

start = startDatePicker.Value.ToShortDateString();
end = endDatePicker.Value.ToShortDateString();

To:

start = startDatePicker.Value.ToString("yyyy-MM-dd HH:mm:ss")
end = endDatePicker.Value.ToString("yyyy-MM-dd HH:mm:ss")

Then I believe this should work fine.

Answer 3

In your code, there are two (obvious) possibilities for a failure.

The first would be in implicit conversion error in the date column. This would occur if purchase.date were a date/time value, but one of columns in the union all is not: bonus.date, rightshares.date, orsales.date. To be honest, I would be surprised if this is the case. However, it is easy enough to check for. Just run theunion all` query in SSMS to see if an error occurs.

The second possibility is something wrong with @Param1 or @Param2. These seem to be passed as strings. You can test this by looking at the string and just doing:

select cast(@Param1 as datetime), cast(@Parame2 as datetime)

One possible problem is different date formats at the operating system and database levels. In one, for instance, 20/01/2013 might be Jan 20th and in another an error. If this is the problem, you can use convert() to convert the parameters to the right format (in the database) or convert them before passing them.