how to get data of current week only in SQL server?

by

Do it like this:

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT [...]
AND WorkDate >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) 
AND WorkDate <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

Explanation:

  • datepart(dw, getdate()) will return the number of the day in the current week, from 1 to 7, starting with whatever you specified using SET DATEFIRST.
  • dateadd(day, 1-datepart(dw, getdate()), getdate()) subtracts the necessary number of days to reach the beginning of the current week
  • CONVERT(date,getdate()) is used to remove the time portion of GETDATE(), because you want data beginning at midnight.

Leave a Comment