C# Select in SQL Server just using the Date with sql parameters

Question

I am currently trying to do a Select in my SQL Server database using a parameter with Datetime type. But I need this parameter only has the format YYYY-MM-DD date because of the following query in SQL I'm using and it's working :

select 
    idCliente, DescCliente, DescAnalista , StatusChamado , DataAtendimento 
from 
    Atendimento 
where 
    cast ([DataAtendimento] as date) = '2016-04-27';

I read several posts indicating that I use DbType or .ToString but when running it is generating error alleging failure to convert the string to the date / time format.

This is how I use the SqlParameter:

DateTime date;
date = Data;

try
{
    sqlClient.Command.Parameters.AddWithValue("@adate", date);
    sqlClient.Command.CommandText = @" select idCliente, DescCliente, DescAnalista , StatusChamado , DataAtendimento from Atendimento where cast ([DataAtendimento] as date) = '@adate';";

I need a help from you guys , I'm not finding any means that can perform this select

Answer 1

You have to remove the '' in = '@adate'. With the '' in place, it effectively turns your query into:

select 
    idCliente, DescCliente, DescAnalista , StatusChamado , DataAtendimento 
from Atendimento 
where cast ([DataAtendimento] as date) = '@date';

This means that cast([DateAtendimento] as date) is compared against the string '@date', thus the conversion to date error.