Retrieving certain days and hours

Transact-SQL
1

I can't get this to work but I think I am close. I need everything that happened on Saturday and Sunday plus anything that happened before work hours and after work hours M-F

Thanks for your help

select *
from so_hdr
where DATEPART(dw,ADDED_DTE) in (1,7) or 
(DATEPART(dw,ADDED_DTE) in (2,3,4,5,6) and (DATEPART(HH,ADDED_DTE)<cast('07:30' as datetime) or DATEPART(HH,ADDED_DTE)>cast('16:00' as datetime)))
2 
select * /* <--- good practice is specifying each field instead of * */
  from so_hdr
 where datepart(day,added_dte) in (1,7)
    or datepart(hour,added_dte)*100+datepart(minute,added_dte) not between 730 and 1600
1 Like
3

I removed the dependency on the @@DATEFIRST setting for checking the day of week.

select *
from so_hdr
where DATEDIFF(DAY, 0, ADDED_DTE) in (5, 6) /Sat,Sun/ or
(DATEDIFF(MINUTE, 0, ADDED_DTE) % 1440 / 60.0) < 7.5 OR
(DATEDIFF(MINUTE, 0, ADDED_DTE) % 1440 / 60.0) > 16.0

/* A different way to test the time would be:
NOT (DATEADD(DAY, -DATEDIFF(DAY, 0, ADDED_DTE), ADDED_DTE) BETWEEN '07:30' AND '16:00')
*/

1 Like
4

This works. Thank you both!