SQL to use getdate() to declare a variable to run a comparison against in SAP B1

Question

I have a SAP B1 installation and I have a user table consisting of WeekNo, DateFrom, DateTo ... It lists all the week numbers for each year, so the first week of the year is 202001 (yyyyww) and is set DateFrom 01/01/2020 and DateTo 07/01/2020

I am using getdate() to fetch todays date and using that to get the current week number. What I am unable to see past is how to set the current week number to run a comparison against.

I have agoods in table also which has a field and is auto populated with the week number when it is created.

I need to be able to run this query without the user needed to enter the date or week number and for it to return all the goods in records for the current week.

So, getdate() to fetch the current week. Compare that returned week (as a string) to all the week numbers (as a string) against all the goods in records and return where it matches.

Here is my sql, which is very incomplete.

getdate() as 'Date',
T1.[ItemCode], 
T1.[Quantity] 
FROM 
OPDN T0  
INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN [dbo].[@DATE]  T2 ON T0.[U_Wk] = T2.[Code]
WHERE getdate() BETWEEN T2.U_From and T2.U_to 
AND T1.[ItemCode] NOT Like '%%STAT%%' 

To explain the tables

T1.[Quantity] <-- number of each
T0/T1.[DocEntry] <-- GR record ID
T0.[U_Wk] <-- week number on the GR
T2.[Code] <-- week number used for the getdate() clause
T2.U_From and T2.U_to  <-- dates that denote the week start and end

Answer 1

my apologies if I have misunderstood your question but this should help:

get current week

 select DATEPART(wk, GETDATE())

get the document week number

 select datepart(week, DocDate) from ORDR

for example

declare @thisWeek as nvarchar = cast(DATEPART(wk, GETDATE()) as nvarchar)
select 
docnum
from ORDR
where cast(datepart(week, DocDate) as nvarchar) = @thisWeek