Query with Datetime Sql and C #

Question

Query with Datetime Sql and C #

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

I'm trying to get the patient back to me for a certain day and time in the following command:

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 are returning all patients for the day.

I tried to change as date to as datetime , when I do this it returns null , I tried to convert datetime to date and pass straight but it will not.

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

I changed it to be 2018-11-10 17:35:00.000 , but it still was not.

What should I do?

sql sql-server c# asp.net

asked by anonymous 15.11.2018 / 21:19

2 answers

What is the real need of the "initialize" method in Ruby? Extracting data from a data frame in R

score 3 · Answer 1

If you want to query date and time, you can not do a cast for type date because this type has no time information, only date. I assume your Consulta.dataConsulta field is set to type datetime in the database, so you would not need to cast cast for this type either.

You said that the datetime being searched is 2018-11-10 17:35 , and that you tried the 2018-11-10 17:35:00.000 format as well. This second format would be the correct one for a field search of type datetime ( parts and minutes parts, but the seconds and milliseconds are zeroed. That is, if the query was written to the database as 2018-11-10 17:35:17.003 , you will not find it.

If you want to find a query with 17:35 you will probably have to check the times between 17:35:00.000 and 17:35:59.999 , and the code can look 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);

score 1 · Answer 2

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

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

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