Getting the first day in a week with T-SQL

Comments

1. 

   Donna 2 years ago

   I've been searching around the internet for a simple solution to get the first date of the week for a couple of hours. Thank you for this post, it was exactly what I needed!
2. 

   Graham Charles 1 years ago

   Useful, although for me this always returns a Monday -- and not the day set in SET DATEFIRST. I'm still puzzling over why...
3. 

   GG 1 years ago

   This gets me always the first Monday in a week, but not the first day of the week, nor the Monday of a week assuming Monday is the first day. For example, if I use a Sunday instead of getdate(), it'll return the following day, the Monday, not the Monday six days earlier.
4. 

   Pete 1 years ago

   Cool I prefer this to other methods that may modify the datefirst sql server property. This is much safer for me and can be used in functions where datefirst can't be set.
   
   As with GG if you put Sunday it will return the following day. A lot of times you want the Monday before the date specified but it can be solved easily with a case statement like below:
   
   select case
   WHEN DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0) > GetDate() THEN
   DATEADD(ww, -1, DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0))
   ELSE
   DATEADD(ww, DATEDIFF(ww,0,GetDate()), 0)
   END
5. 

   Pete 1 years ago

   @Graham DATEDIFF doesn't respect the DATEFIRST property. In my opinion a good thing as I have seen things go wrong with date calculations when it comes into play.
6. 

   Johan 1 years ago

   The script uses the fact that 1900-01-01 was a Monday.
   That is why the result always is a Monday.
   
   If you want to use another day then Monday as start day of the week I would suggest using this script instead:
   

   SELECT DATEADD(DD, 1-DATEPART(DW, GETDATE()), CONVERT(INT, GETDATE()))

   
   Verify what day is the first of the week by using:
   

   SELECT @@DATEFIRST

   
   And change it if needed by using:
   

   SET DATEFIRST

   
   
7. 

   Andy 1 years ago

   i used this simple technique, returns sunday of the current week.
   
   Declare @Sunday DateTime
   Set @Sunday = (DateAdd(d, - (DatePart(dw,GetDate()) -1), GetDate()))
8. 

   Ruwan 10 months ago

   Thanks very much
9. 

   Hamish 9 months ago

   Awsome skills man, took me ages to find this but well worth it.!!
   
   Cheers
   
10. 

    MK 7 months ago

    Nice code.
    
    This is how I get Monday of that week:
    select getdate() + 7-datepart(weekday, getdate())-5
11. 

    Ryan 6 months ago

    Thanks for sharing this! I have read other forum posts about this same functionality and they all suggested using variables. This allows me to look at a records date, and scale a report by the Year, Month, Week, and then days. Thanks!!!