sql begin end week of year

Question

I do cant get the begin and end week between two dates in Sql Server? for example:

    begin       end
    2016-04-25  2016-05-01
    2016-05-02  2016-05-08
    2016-05-09  2016-05-15
    2016-05-16  2016-05-22
    2016-05-23  2016-05-29
    2016-05-30  2016-06-05
...

Regards!

Which `DBMS` you are using. Did you try anything – Pரதீப் May 16 '16 at 16:07 — Pரதீப், May 16 '16 at 16:07

score 0 · Answer 1 · answered May 16 '16 at 16:11

0

SQL has the DATEDIFF(datepart,startdate,enddate) function for this purpose.

Example:

SELECT DATEDIFF(day,'2014-06-05','2014-08-05') AS DiffDate

results in

Learn more about DATEFIFF here

answered May 16 '16 at 16:11

Luca Schimweg

747
5
18

thx, I dont need datediff, I need get 'dates' for begin of week and end, day begin monday for example. – Nelson Calderon May 16 '16 at 16:17
How do you mean that? Which date "for begin of week"? @NelsonCalderon – Luca Schimweg May 16 '16 at 16:20

score 0 · Answer 2 · answered May 16 '16 at 16:11

0

use DATEDIFF() function in SQL Server

answered May 16 '16 at 16:11

elfico

450
1
6
13

score 0 · Answer 3 · edited May 23 '17 at 11:44

You need a date table to do this

Declare @start_date datetime = '2016-04-25'
        @end_date datetime = '2016-06-05'

SELECT Min(dt) AS week_start, 
       Max(dt) AS week_end 
FROM   dates_table 
WHERE  date_col > @start_date 
       AND date_col < @end_date 
GROUP  BY Datepart(week, dt), 
          Year(dt) 
ORDER  BY Datepart(week, dt), 
          Year(dt)

To create a date table and populate dates, Here is few question about generating dates between range of dates

score 0 · Accepted Answer · answered May 16 '16 at 16:33

0

Try this :

DECLARE @d1 DATE = '2016-04-20' , @d2 DATE = '2016-06-07'
, @mon1 DATE
;WITH 
sunA AS (
  SELECT k=1, dt = @d1, dn = datename(dw,@d1)
  UNION ALL 
  SELECT k=k+1, dt = DATEADD(d,1,dt), dn = datename(dw,DATEADD(d,1,dt))
  FROM sunA 
  WHERE k<7
) 
  SELECT TOP 1 @mon1 = dt 
  FROM sunA
  WHERE dn = 'Monday'
  ORDER BY k

DECLARE @weeks TABLE(d1 DATE, d2 DATE)
WHILE @mon1 >= @d1 AND DATEADD(d, 6, @mon1) <= @d2
BEGIN
  INSERT INTO @weeks SELECT @mon1, DATEADD(d, 6, @mon1)
  SET @mon1 = DATEADD(d, 7, @mon1)
END

SELECT * FROM @weeks

-- OUTPUT
d1          d2
2016-04-25  2016-05-01
2016-05-02  2016-05-08
2016-05-09  2016-05-15
2016-05-16  2016-05-22
2016-05-23  2016-05-29
2016-05-30  2016-06-05

answered May 16 '16 at 16:33

leoinfo

7,860
8
36
48

hello @leoinfo is perfect, this is the function that I needed, thank you very much, wow!!! – Nelson Calderon May 16 '16 at 16:42
@NelsonCalderon - I don't think `CTE` works in `SQL Server 2005` – Pரதீப் May 16 '16 at 16:51
@Prdp - CTE was introduced with SQL Server 2005 – leoinfo May 16 '16 at 18:23

sql begin end week of year

4 Answers4