CODEWITHSUNDEEP
sqloracle-database
Erdi İzgi
Erdi İzgi

Reputation: 1322

Oracle SQL: How to check if a calendar week is in between two calendar weeks

I want to check if a calendar week is in between two calendar weeks using PL/SQL.

Let's say the calendar week I want to check is 26. When I check if this is between 24 and 27, then it should return true.

This part is easy I can use BETWEEN X AND Y and this solves the problem.

However if the calendar week I want to check is 52 and If I want to check if this is between 50 and 3, this should return true. As you realized, using BETWEEN X AND Y won't give the right result here.

Well, I can check which calendar week is greater, and I can do the comparisons based on that but this doesn't sound like an elegant solution to me. What would you suggest in this case?

Upvotes: 3

Views: 195

Answers (2)

Indent
Indent

Reputation: 4967

You can't compare only 2 week number, because any week number is alway between 2 another week !

You need precise the YEAR and have a fixed format with leading zero :

example :

'2017-W52' is     between '2017-W50' and '2018-W03'

'2018-W52' is not between '2017-W50' and '2018-W03'

String containing date can be compare using ISO-8601 format

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You can use logic like this:

where (x <= y and v_week >= x and v_week <= y) or
      (x > y and (v_week <= x or v_week >= y))

You can almost write this as:

where (x <= y and v_week between x and y) or
      (x > y and v_week not between x and y)

However, this is incorrect for the second case when v_week in (x, y).

Upvotes: 0

Related Questions