CODEWITHSUNDEEP
mysqlsqlsql-server-2008
Uuid
Uuid

Reputation: 2546

Sql Server 2008, Select dates of current week in a generic query?

How can I get the current week in a generic query, currently I can get a date range but I would like to get the current week in a dynamic way.

This is what I have:

WITH mycte AS
(
  SELECT CAST('2011-01-01' AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte   
  WHERE   DateValue + 1 < '2021-12-31'
)
SELECT  DateValue
FROM    mycte
OPTION (MAXRECURSION 0)

Based on todays date I want to get something like:

11-04-2013 11-05-2013 11-06-2013 11-07-2013 11-08-2013 11-09-2013 11-10-2013

Upvotes: 1

Views: 689

Answers (1)

peterm
peterm

Reputation: 92785

One way of doing it in SQL Server

WITH weekdays AS 
(
  SELECT 0 day
  UNION ALL
  SELECT day + 1 FROM weekdays WHERE day < 6
)
SELECT DATEADD(DAY, day, DATEADD(DAY, 2-DATEPART(WEEKDAY, CONVERT (date, GETDATE())), CONVERT (date, GETDATE()))) date
    FROM weekdays

Output:

|       DATE |
|------------|
| 2013-11-04 |
| 2013-11-05 |
| 2013-11-06 |
| 2013-11-07 |
| 2013-11-08 |
| 2013-11-09 |
| 2013-11-10 |

Here is SQLFiddle demo

In MySQL

SELECT CURDATE() + INTERVAL 1 - DAYOFWEEK(CURDATE()) DAY + INTERVAL day DAY date
  FROM
(
  SELECT 1 day UNION ALL 
  SELECT 2 UNION ALL 
  SELECT 3 UNION ALL 
  SELECT 4 UNION ALL 
  SELECT 5 UNION ALL 
  SELECT 6 UNION ALL 
  SELECT 7
) w

Here is SQLFiddle demo

Upvotes: 1

Related Questions