How to compare a date of type datetime?

Question

I have a date in a patient table in the Sql Server database in the following format:

datetime (01/01/2016 12:50:00.000)

I want to check if the patient’s appointment time has arrived.

For example: The time of the consultation is at 13:00, I want to check if today is the scheduled day and if it is the scheduled time. How do I?

Answer 1

The most simplified way to do this is simply by comparing one date to the other where data = @Data, see the examples below.

declare @Data datetime = cast('01/01/2016 12:50:00.000' as datetime)
declare @paciente table
(
  id int,
  data datetime
)

insert into @paciente values
(1, GETDATE()),
(2, cast('01/01/2016 12:50:10.001' as datetime)),
(3, cast('01/02/2016 12:50:00.000' as datetime)),
(4, cast('01/01/2016 12:40:00.000' as datetime)),
(5, cast('01/01/2016 12:50:00.000' as datetime))

select * from @paciente
where data = @Data


-- ou 
select * from @paciente
where cast(data as date) = cast(@Data as date)
and cast(data as time) = cast(@Data as time)

-- ou 
select * , convert(varchar, data, 108) from @paciente
where year(data) = year(@data) 
and month(data) = month(@data) 
and day(data) = day(@data) 
and convert(varchar, data, 108) = convert(varchar, @data, 108)

See some tips on datetime.

Reference 1

Reference 2

Reference 3

Reference 4