CODEWITHSUNDEEP
mongodbaggregation-frameworkweek-numberdatepart
alexs
alexs

Reputation: 1856

$week mongodb to datepart(wk)

I'm exporting aggregated data to MS SQL server from mongodb

mongo's $week does not evaluate to the same week as T-SQL datepart(wk, ) or datepart(isowk, ).

Does anyone know how to test the mongodb $week function so I can do some comparisons and see how best to resolve this issue?

Any help would be appreciated.

Upvotes: 1

Views: 393

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151132

As far as I can see the differences fall into two main areas, the plain part being described in the documentation:

Weeks begin on Sundays, and week 1 begins with the first Sunday of the year. Days preceding the first Sunday of the year are in week 0. This behavior is the same as the “%U” operator to the strftime standard library function.

So the general concept here is that the value returned will be between 0 and 53 inclusive with week 0 being defined as the first week when the days are before Sunday.

So to paraphrase ( because the documentation gets the days wrong ) from the technet source for "datepart":

January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

And then (corrected):

The following table lists the return value for week and weekday datepart for '2007-04-21 ' for each SET DATEFIRST argument. January 1 is a Saturday in the year 2007. April 21 is a Saturday in the year 2007. SET DATEFIRST 7, Sunday, is the default for U.S. English.

So where you had documents like this:

{ "date" : ISODate("2007-01-01T00:00:00Z") }
{ "date" : ISODate("2006-12-31T00:00:00Z") }
{ "date" : ISODate("2006-01-01T00:00:00Z") }

The $week operator would return such as this:

{ "date" : ISODate("2007-01-01T00:00:00Z"), "week" : 0 }
{ "date" : ISODate("2006-12-31T00:00:00Z"), "week": 53 }
{ "date" : ISODate("2006-01-01T00:00:00Z"), "week" : 1 }

January 1st in 2006 was a Sunday and is considered the start of week 1, but where it was a Saturday it would be week 0 of 2007. The 31st of December in the previous year is Week 53.

In contrast the DATEPART considers Jan 1st 2007 and Dec 31st 2006 to be in Week 1 as the week ending on the first Sunday of the year. The Sunday is the default US English value but may differ and can in fact be set via SET DATEFIRST

So both have different opinions of where a date belongs in terms of the week of the year, but the general difference will be one with the other consideration days falling at the end of the previous year.

Upvotes: 1

Related Questions