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

I can not believe I was arrested hours because of these quotes , it worked perfectly , thank you — André Freindorfer
– André Freindorfer, Commented Apr 28, 2016 at 2:32

Felix Pamittan · Accepted Answer · 2016-04-28 02:34:27Z

5

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.

answered Apr 28, 2016 at 2:34

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

André Freindorfer Over a year ago

That was exactly what was happening, I do not think I was hours researching and trying to convert the date thinking that the problem was it , thank you for help

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related