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?
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: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);
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.