CODEWITHSUNDEEP
sql-serverdatedelphi
SiBrit
SiBrit

Reputation: 1530

Replicate SQL Server DATEPART(wk, ...) function in Delphi

Delphi's WeekOfTheYear function uses the ISO 8601 methodology where the week starts on a Monday, and the first week of a year is defined as the first week with four or more days in that year.

To get the week of the year in Microsoft SQL Server, you use the DATEPART(wk, ...) function, but this uses a different mechanism, in that the default for U.S. English starts the week on Sunday, and that the 2nd week of the year is the first Sunday of the year, unless that Sunday is the 1st of January, then the 2nd week starts on the 2nd Sunday of the year, as can be seen if you run this example:

SELECT '2010-1-3', DATEPART(wk, '2010-1-3'), DATENAME(dw, '2010-1-3')
SELECT '2011-1-2', DATEPART(wk, '2011-1-2'), DATENAME(dw, '2011-1-2')
SELECT '2012-1-1', DATEPART(wk, '2012-1-1'), DATENAME(dw, '2012-1-1')
SELECT '2013-1-6', DATEPART(wk, '2013-1-6'), DATENAME(dw, '2013-1-6')
SELECT '2014-1-5', DATEPART(wk, '2014-1-5'), DATENAME(dw, '2014-1-5')
SELECT '2015-1-4', DATEPART(wk, '2015-1-4'), DATENAME(dw, '2015-1-4')
SELECT '2016-1-3', DATEPART(wk, '2016-1-3'), DATENAME(dw, '2016-1-3')
SELECT '2017-1-1', DATEPART(wk, '2017-1-1'), DATENAME(dw, '2017-1-1')
SELECT '2018-1-7', DATEPART(wk, '2018-1-7'), DATENAME(dw, '2018-1-7')
SELECT '2019-1-6', DATEPART(wk, '2019-1-6'), DATENAME(dw, '2019-1-6')
SELECT '2020-1-5', DATEPART(wk, '2020-1-5'), DATENAME(dw, '2020-1-5')

The results from above show every Sunday being the 2nd week of the year, except for the 2 that are the 1st January, which show the 1st week of the year.

I looked at the answer for the question below, that seemed to indicate it would return the week of the year depending on what day the week starts,

Delphi week number function based on system start of week

but that is also based on using the Delphi functions, and does not return the same results as SQL Server.

What SQL Server currently shows for the dates 1st January 2018 to 8th January 2018 is:

1/1/2018 = 1
2/1/2018 = 1
3/1/2018 = 1
4/1/2018 = 1
5/1/2018 = 1
6/1/2018 = 1
7/1/2018 = 2
8/1/2018 = 2

Delphi does have a DayOfWeek function that uses Sunday as the first day of the week, compared to the DayofTheWeek function which uses Monday, but I just can't seem to work out the logic that is needed to get the same results as SQL Server. There has to at least be a condition in there for dealing with Sunday 1st January being week 1, but for the first other Sundays being week 2.

Does anyone have any Delphi source code that returns the week of the year exactly as SQL Server does when using U.S. English default of DATEFIRST 7?

Upvotes: 1

Views: 350

Answers (1)

Dsm
Dsm

Reputation: 6013

The code is deceptively simple, but the logic of it is a little difficult. The rule about the first Sunday means that, in all cases the first Saturday is in week 1. So the idea is that we go to the next Saturday and count the number of Saturdays from the start of the year.

function SQLWeekOfYear(const pDate: TDate): integer;
var
  iYear, iMonth, iDay : word;
  iDOW : integer;
  iDays : integer;
begin
  // this is based on First Sunday in year being in week 2
  // unless it is the first of Jan, which is equivalent of saying that the
  // first saturday is always in week 1.
  //
  // This means that we count the number of saturdays prior to this date and add one,
  // which is equivalent to finding the next saturday
  DecodeDate( pDate, iYear, iMonth, iDay );
  iDOW := DayOfWeek( pDate );
  // Find how many days so far since 1st jan
  iDays := Trunc(pDate - EncodeDate( iYear, 1, 1 )) + 1;
  // now adjust for day of week to get to next saturday
  iDays := iDays + (7-iDOW);
  // add 6 and divide by 7 to find actual number saturdays
  Result := (iDays + 6) div 7;
end;

Upvotes: 1

Related Questions