Home / Questions / 548844
Accepted
Efrain Mejias C
Asked: 2022-07-29 03:40:58 +0800 CST 2022-07-29 03:40:58 +0800 CST

How to update records by field of type TIME in SQLSERVER?

  • 772

I have a table that has a DATETIMEname type field AppointmentDatewhere it saves data with format 2022-07-27 21:32:54.420 and another field of TIMEname type EndHourand saves data with format 13:00:00.0000000.

So you needed a query to update the records where the date is Less than or Equal to field AppointmenDateand Greater than field EndHour.

I implemented this code, but it's not working for me, because what I needed was for it to compare AppointmentDateonly the date part against the field and EndHouronly the time part against the field. (I thought that only comparing AppointmentDate against the current date would do it but it doesn't work, you should also include the comparison against EndHour but I don't know how to do it)

UPDATE ScheduledVirtualAppointments SET  IdState = @idStateDone , DateUpdate = GETDATE(), IdUserUpdate = 1
WHERE AppointmentDate < GETDATE() AND 
IdState NOT IN (@idStateDone,@idStateRejection,@idStateCancellation,@idStateQuizAnswers)

How can i fix this?

enter image description here

sql

2 Answers

  1. According to your question what you are looking for is to filter in the update:

    • Less AppointmentDatethan or equal to today
    • The current time greater thanEndHour

    You could use CAST()to keep each part of a date/time:

    DECLARE @Now DATETIME = GETDATE()

UPDATE ScheduledVirtualAppointments 
       SET  IdState = @idStateDone , DateUpdate = GETDATE(), IdUserUpdate = 1
       WHERE CAST(AppointmentDate AS DATE) <= CAST(@Now AS DATE)
             AND CAST(@Now AS TIME) > EndHour
             AND IdState NOT IN (@idStateDone, @idStateRejection, 
                                 @idStateCancellation, @idStateQuizAnswers)

    Note: We use a variable @Nowfor the current date since it GETDATE()is a non-deterministic function, that is, each execution will surely return a different value and since it must be used more than once in the query, it is preferable to always handle the same date/time.

    • 2
  2. Best Answer

    Thank you Patricio Moracho.

    The complete code looks like this:

        DECLARE @Now DATETIME = 
(SELECT CONVERT(DATETIME, SWITCHOFFSET(GETDATE(), DATEPART(TZOFFSET, GETDATE() AT TIME ZONE 'Central Standard Time'))))
               
    UPDATE ScheduledVirtualAppointments  SET  IdState = @idStateDone , DateUpdate = GETDATE(), IdUserUpdate = 1 
WHERE CAST(AppointmentDate AS DATE) <= CAST(@Now AS DATE) AND CAST(@Now AS TIME) > EndHour 
AND IdState NOT IN (@idStateDone, @idStateRejection, @idStateCancellation, @idStateQuizAnswers)
    • 0