I'm a passionate web developer and systems architect living in Stockholm, Sweden. I work at Valtech and I am an EMVP.
About web development with ASP.NET and EPiServer, fatherhood and other adventures in my digital jungle
A colleague just asked me if I knew a way to get the first day in a week in SQL Server. While I'm sure that there are many ways to get the first day (date) in a week here is a very simple way that in my opinion is quite elegant. The example assumes that you want to get the date of the first day in the week that GETDATE() is in. You could of course replace GETDATE() with a column of the datetime type.
SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)
The same method can be used to extract only the date part from a datetime in T-SQL:
DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
PS. For updates about new posts, sites I find useful and the occasional rant you can follow me on Twitter. You are also most welcome to subscribe to the RSS-feed.
This blog is built with EPiServer Community, EPiServer CMS, ASP.NET MVC and a bunch of other great products. The source code is available for download at the projects page, where you also can read more about this site and my other projects.
read more
Comments
Donna 6 months 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!
Graham Charles 2 months ago
Useful, although for me this always returns a Monday -- and not the day set in SET DATEFIRST. I'm still puzzling over why...
GG 13 days 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.