Here is a function that returns the startDate of a week
CREATE FUNCTION RetStartOfWeek (
@DATE datetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4 Thu = 5, Fri = 6, Sat = 7 Default to Sunday @StartofWeek int = 1
)
/*Find the fisrt date on or before @DATE that matches day of week of @StartofWeek.*/
RETURNS datetime
AS
BEGIN
DECLARE @StartOfWeekDate datetime
DECLARE @FIRST_BOW datetime
-- Check for valid day of week
IF @StartofWeek between 1 and 7
BEGIN
-- Find first day on or after 1753/1/1 (-53690)
-- matching day of week of @StartofWeek
-- 1753/1/1 is earliest possible SQL Server date.
SELECT @FIRST_BOW = CONVERT(datetime,-53690+((@StartofWeek+5)%7))
-- Verify beginning of week not before 1753/1/1
IF @DATE >= @FIRST_BOW
BEGIN
SELECT @StartOfWeekDate = DATEADD(dd,(DATEDIFF (dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
END
END
RETURN @StartOfWeekDateend
Ads 720 x 90
Total Pageviews
Label List
- Architecture
- Art of SQL Server
- asynchronous mirroring
- csv
- dmv
- find outdated Statistics
- Free SQL Server tools
- index fragmentation
- Installation
- Katmai
- Learn SQL Server 2005
- Maintenance Plan
- outdated Statistics
- Reset Identity column
- Scripts
- SPID
- sql server 2008
- sys.dm_db_index_physical_stats
- sys.dm_exec_connections
- tsql
- update statistics