cancel
Showing results for 
Search instead for 
Did you mean: 

Query with proper week number: Tuesday - Monday

Former Member
0 Kudos

Dear all,

I need to retrieve week numbers from Docdate start - Tuesday end - Monday, the below example query is for Monday - Sunday

SELECT

T0.[DocNum],

T0.[DocDate],

DATEPART (wk, T0.Docdate) AS 'WEEK NUMBER',

YEAR (T0.Docdate) AS 'YEAR'

FROM OINV T0

WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]

Thank you for your help.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Try:

SELECT

T0.[DocNum],

T0.[DocDate],

DATEPART (wk, T0.Docdate+1) AS 'WEEK NUMBER',

YEAR (T0.Docdate) AS 'YEAR'

FROM OINV T0

WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]

Former Member
0 Kudos

Hi,

Please clarify your definition of the week number. Is it related 52 weeks in a year?

Thanks,

Gordon

Former Member
0 Kudos

Hi,

Most likely it will be 53 weeks this way depending on year as this one started on tuesday

Thanks,

Tomasz

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi Tomasz Bolesta,

I think you need to change settings before run the query.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Are there any other solutions without need for modification of system settings ?

Thank you,

Tomasz

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try with the following functions:

Datediff(DD,date1,date2) and datepart(dw,date)

Thanks & Regards,

Nagarajan