Phakirus Phakirus - 1 month ago 17
SQL Question

Update SQL but only on fridays

enter image description hereIn a database I have two columns, one with DATE and another with HOUR, where I have to look in the HOUR column for a time interval and change. And also look for every Friday and put also in a specific HOUR but nothing is working.

This is the code to search for the HOUR and change, and is already working:

UPDATE [dbo].[Clockings] set [Hour] = DATEADD(second, (ABS(CHECKSUM(NEWID())) % 900), '07:45:00.000') where [Hour] > '04:00:00.000' and [Hour] < '07:45:00.000'


UPDATE [dbo].[Clockings] set [Hour] = DATEADD(second, (ABS(CHECKSUM(NEWID())) % 900), '17:30:00.000') where [Hour] > '17:45:00.000' and [Hour] < '23:59:59.000'

To search the DATE table nothing worked.
enter image description here

Answer

Answer for 2012 or later:

Update Clockings
    Set ...
    Where DatePart(dw, [Date]) = 5; --Friday

Note, check which day is the first day of the week. https://msdn.microsoft.com/en-au/library/ms181598.aspx

DateName() function may also suit.