Phakirus Phakirus - 1 year ago 86
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 Source

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.

DateName() function may also suit.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download