<< Click to Display Table of Contents >> Navigation:  SQL > SQL - solving issues > Day of Year, Week Number

See also BDE System settings

Calculating Day of Year and Week Numbers

Provided the current BDE Admin setting for the date separator is "/" as in:

the day of year and week numbers can be calculated as below.
If using a different BDE date separator setting, the separator in CAST('1/1/' + must be changed correspondingly.

Day of Year

This is calculated by first calculating the first day of the year relative to the date (year) of focus by:
CAST('1/1/' + CAST(EXTRACT(YEAR FROM DateField) AS CHAR(4)) AS DATE

and from this calculating the virtual "date zero" of this year by:

CAST('1/1/' + CAST(EXTRACT(YEAR FROM DateField) AS CHAR(4)) AS DATE)-1

which as the next step is subtracted form the date or datetime of focus:
DateField - (CAST('1/1/' + CAST(EXTRACT(YEAR FROM DateField) AS CHAR(4)) AS DATE)-1)

and finalized as the full expression converting into an integer day number of the year:

CAST((DateField - (CAST('1/1/' + CAST(EXTRACT(YEAR FROM DateField) AS CHAR(4)) AS DATE)-1)) AS INTEGER)
AS DayOfYear

Week Number

In much the same way week numbers can be calculated, however not only using the pre 1. January as the zero point, but also manually entering the skew of days before the first day of the first week of the year, whatever counting Mondays or Sundays as first day of week, and whatever the first week of the year is considered the week including the four first days of the year by some other first week principle.

As BDE Local SQL does not provide a day of week function, the expression as below must be adapted for each year on calculating the week numbers through modifying the iJan1stFromMondayWeek1 according to each calendar year.

As part of the final expression, the virtual "date zero" is calculated in much the same way for Day of Year:

CAST('1/1/' + CAST(EXTRACT(YEAR FROM DateField) AS CHAR(4)) AS DATE)-1

but to calculate the first week's offset, this has to be entered manually at the iJan1stFromMondayWeek1 placeholder, whatever this first day of the first week is in January or within one of the last days of the preceding December:

CAST((DateField - (

CAST('1/1/' + CAST(EXTRACT(YEAR FROM DateField) AS CHAR(4)) AS DATE)-1

) +6 +iJan1stFromMondayWeek1)AS INTEGER)/7 

AS WeekNr

If the week 1 starts a few days after the New Year, e.g. 1. Jan. on a Saturday, then 1. Jan. is shiftet two days to the left of week 1 counted from the succeding Monday, and iJan1stFromMondayWeek1 should subtracted having the value of -2, whereas if the first week starts ahead of the New Year, e.g. on Monday 30. December and 1. Jannuary being a Wednesday, then the number of days 1. January is skewed to the right from Monday of week 1 should be added (prefixed by a plus) as in:
iJan1stFromMondayWeek1 = + 2.

__________________________
PdxEditor Application Help, 21 May 2026; © 2010-2026 Niels Knabe