Dates are driving me mad!! please help

Jacktheripperz — Tue, 22 Jul 2025 12:01:19 GMT

Hi I have a simple data load using sql

if i run in sql developer.

Select
trunc(DATECOLUMN1),
to_char(DATECOLUMN1 + 4, 'YY') || '-' ||to_char(to_date(A.DATECOLUMN1, 'DD-MM-YYYY HH24:MI:SS') +4, 'IW')
FROM TESTING A
where to_char(to_date(A.DATECOLUMN1, 'DD-MM-YYYY HH24:MI:SS') +4, 'IW') = '28'

I get dates 05-JUL-25 to 11-JUL-25 for week 28 this is exactly what I want the week to start on a saturday and to end on a fridy (custom week)

if I do a Qlik load

LIB CONNECT TO 'TESTINGDATA';
QUALIFY *;
// Ensure ISO week logic
SET FirstWeekDay=0;     // Monday
SET BrokenWeeks=0;      // Only full weeks
SET ReferenceDay=4;     // Thursday-based week start (ISO)
SET DateFormat='DD-MMM-YY';

[test]:
SELECT
TRUNC(A.DATECOLUMN1) AS TRUNC_DATE,
TO_CHAR(A.DATECOLUMN1 + 4, 'YY') || '-' || TO_CHAR(A.DATECOLUMN1 + 4, 'IW') AS WEEK_LABEL
FROM TESTING A
WHERE TO_CHAR(A.DATECOLUMN1 + 4, 'IW') = '28';

I get dates 07-JUL-25 to 13-JUL-25 for week 28 this isnt 1 day out it is 2 days off. I just cant seem to get the sql to behave?

Re: Dates are driving me mad!! please help

Jacktheripperz — Wed, 23 Jul 2025 08:18:34 GMT

as I didn't reply I am leaving this here for anyone in the same boat.
I couldn't fix it in the sql so I fixed it in the autocalendar load

this is the line that you need

Dual(
'W' & Num(Floor((Floor($1) - Floor(MakeDate(2025, 7, 5))) / 7) + 28, '00'),
Floor((Floor($1) - Floor(MakeDate(2025, 7, 5))) / 7) + 28
) AS [Week]

this rightly or wrongly locks the week number to the 5/7/25 that is week 28 and so the rest of the weeks fall into line. I have some more testing to do and then I will use this to filter etc on

Dual(
Num(Year($1) - 2000, '00') & '-' & Num(Floor((Floor($1) - Floor(MakeDate(2025, 7, 5))) / 7) + 28, '00'),
Floor((Floor($1) - Floor(MakeDate(2025, 7, 5))) / 7) + 28
) AS [Year-Week],

topic Re: Dates are driving me mad!! please help in Connectivity & Data Prep

Dates are driving me mad!! please help

Re: Dates are driving me mad!! please help