Previous Monday & previous Sunday’s date based on today’s date

by

Easy:

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

EDIT:

The below will handle the Sunday date issue.

DECLARE @input varchar(10)
--SET @input="9/9/2012" -- simulates a Sunday
SET @input = GETDATE()

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 6)

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. How do I UPDATE from a SELECT in SQL Server?
  3. Get top 1 row of each group
  4. How to return only the Date from a SQL Server DateTime datatype
  5. How to calculate age (in years) based on Date of Birth and getDate()
  6. CROSS JOIN vs INNER JOIN in SQL
  7. Referring to a Column Alias in a WHERE Clause
  8. Function vs. Stored Procedure in SQL Server
  9. converting Epoch timestamp to sql server(human readable format)
  10. How to get difference between two rows for a column field?
  11. How do I split a delimited string so I can access individual items?
  12. Common Table Expression, why semicolon?
  13. What’s the best way to select the minimum value from several columns?
  14. T-SQL split string based on delimiter
  15. Counting DISTINCT over multiple columns
  16. SQL Server SELECT into existing table
  17. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  18. Why use Select Top 100 Percent?
  19. How to check if a stored procedure exists before creating it
  20. Correct use of transactions in SQL Server
  21. How do I prevent a database trigger from recursing?
  22. What is the Null Character literal in TSQL?
  23. SQL Server : SUM() of multiple rows including where clauses
  24. Executing SQL Server Agent Job from a stored procedure and returning job result
  25. how does SELECT TOP works when no order by is specified?
  26. is it possible to select EXISTS directly as a bit?
  27. Database Naming Conventions by Microsoft?
  28. Using CASE Statement inside IN Clause
  29. SQL Convert Week Number to Date (dd/MM)
  30. How to group by a Calculated Field

Leave a Comment