Query with Datetime Sql and C#

Asked

Viewed 404 times

4

I’m trying to get you to return to me the patient of a particular day and time in command next:

SqlCommand cmdSelect = new SqlCommand("select DISTINCT P.nome,P.codPaciente, 
M.codMedico, C.descricao from Consulta as C " +
", Medico as M, Paciente as P where M.codMedico = C.codMedico and " +
"P.codPaciente = C.codPaciente and M.cpf = @cpf and " +
"cast(C.dataConsulta as date) = @data",conexao);
cmdSelect.Parameters.AddWithValue("@cpf", Session["cpf"]);
cmdSelect.Parameters.AddWithValue("@data", data);

But you’re returning all the patients of the day.

I tried to change as date for as datetime, when I do it he returns null, tried to convert into datetime the date and pass through but will not.

The date is being passed this way: 2018-11-10 17:35.

I changed her to be 2018-11-10 17:35:00.000, but it still wasn’t.

What should I do?

c# sql sql-server asp.net
  • 1

    If the field type is Datetime then remove the cast. ex: C.dataConsulta=@data

  • the dataConsult column is of what type?

  • Hi William,if I remove the cast it won’t,I tried to do this by also changing the value passed on @data to datetime.but it wasn’t..

  • the dataConsult is datetime

  • in my view then you should not give cast to date on it so since you need the time and this will make you lose the information, the date is a string or a datetime?

  • How is the @data parameter declared? If it is a string, I suggest you use "C.dataConsulta = convert(datetime, @data, 120)",conexao);

Show 1 more comment

2 answers

4

If you want to consult date and time, can not make a cast for the guy date because this type has no time information, only date. I imagine your field Consulta.dataConsulta is defined with the type datetime in the database, so you wouldn’t have to do a cast for that guy.

You said the date-time being searched is 2018-11-10 17:35, and who also tried the format 2018-11-10 17:35:00.000. This second format would be the most correct for a field search of the type datetime (documentation), but, note that in both cases you are only informing the parts of hours and of minutes, but the seconds and millisecond are reset. That is, if the query has been saved in the bank as 2018-11-10 17:35:17.003, you won’t find her.

If you want to find a query to 17:35 you will probably have to check the times between 17:35:00.000 and 17:35:59.999, and the code can be like this:

DateTime dtIni = new DateTime(data.Year, data.Month, data.Day, data.Hour, data.Minute, 0, 0);
DateTime dtFim = new DateTime(data.Year, data.Month, data.Day, data.Hour, data.Minute, 59, 999);

SqlCommand cmdSelect = new SqlCommand(
   "SELECT DISTINCT P.nome, P.codPaciente, M.codMedico, C.descricao" +
   " FROM Consulta AS C, Medico AS M, Paciente AS P" +
   " WHERE M.codMedico = C.codMedico" +
   " AND P.codPaciente = C.codPaciente" +
   " AND M.cpf = @cpf" +
   " AND C.dataConsulta BETWEEN @dataHoraIni AND @dataHoraFim",
   conexao);

cmdSelect.Parameters.AddWithValue("@cpf", Session["cpf"]);
cmdSelect.Parameters.AddWithValue("@dataHoraIni", dtIni);
cmdSelect.Parameters.AddWithValue("@dataHoraFim", dtFim);

2

Information that may be relevant and informative at the same time in this case.

As already mentioned above by fellow @Pedro Gaspar, Date is different from Datetime. In the SQL Server database, whenever a date is recorded without hour/minute/seconds/milliseconds, this date will always behave in value comparison mode as if it were only the day/month/year, if there is a date record with hour/minute/seconds/milliseconds, the comparison of dates will be done in full and will contemplate all the values of the field.

If it is the case to just compare the dates and not your time range (hh:mm:ss), it would be interesting to cast in the field to bring only dd/mm/yyyy.

Browser other questions tagged c# sql sql-server asp.net

You are not signed in. Login or sign up in order to post.