CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
cassandrad
cassandrad

Reputation: 3526

Unexpected behaviour of DATEPART

Script that lists dates and week number of date:

DECLARE @initDate DATE = '2014-01-01';
DECLARE @endDate DATE = '2014-12-31';

WITH dates (date, week)
     AS (SELECT @initDate               date,
                Datepart(ww, @initDate) week
         UNION ALL
         SELECT Dateadd(ww, 1, t.date),
                Datepart(ww, t.date)
         FROM   dates t
         WHERE  t.date < @endDate)
SELECT dates.date,
       dates.week
FROM   dates

The first three rows are:

date:       week number
----------  -----------
2014-01-01  1
2014-01-08  1
2014-01-15  2
.....       ..
.....       ..

I guess it should be 2 for the second row and 3 for the first, isn't it? Is it some kind of bug in DATEPART? Even if something is depends on first day of the year, first row date differs from second on one week with any settings.

Could you clarify this, please?

Upvotes: 1

Views: 53

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

It is because of Recursive CTE not because of Datepart.

In the Recursive part of CTE week is still holding the previous week not the new date that generated in recursive part

Try changing your query like this.

DECLARE @initDate DATE = '2014-01-01';
DECLARE @endDate DATE = '2014-12-31';

WITH dates (date, week)
     AS (SELECT @initDate               date,
                Datepart(ww, @initDate) week
         UNION ALL
         SELECT Dateadd(ww, 1, t.date),
                Datepart(ww, Dateadd(ww, 1, t.date))
         FROM   dates t
         WHERE  t.date < @endDate)
SELECT dates.date,
       dates.week
FROM   dates

Upvotes: 4

Related Questions