CODEWITHSUNDEEP
sqloracle-database
user3157090
user3157090

Reputation: 537

Oracle query for finding the week

We have below query to get the current week number from 1970-01-01, but it is written some time back, can you please tell me exactly why it was subtracting 3 from the subtracted date?

SELECT
TRUNC((to_number(sysdate-to_date('1970-01-01','YYYY-MM-DD'))-3)/7) week from dual

Upvotes: 0

Views: 110

Answers (2)

MT0
MT0

Reputation: 167822

If you put a range of days into it:

SQL Fiddle

Query 1:

SELECT TRUNC((to_number(dt-to_date('1970-01-01','YYYY-MM-DD'))-3)/7)
         week,
       TO_CHAR( dt, 'DY YYYY-MM-DD' ) AS day
FROM ( SELECT TRUNC( SYSDATE, 'IW' ) + LEVEL - 1 AS dt
       FROM   DUAL
       CONNECT BY LEVEL <= 7 )

Results:

| WEEK |            DAY |
|------|----------------|
| 2491 | MON 2017-10-02 |
| 2491 | TUE 2017-10-03 |
| 2491 | WED 2017-10-04 |
| 2491 | THU 2017-10-05 |
| 2491 | FRI 2017-10-06 |
| 2491 | SAT 2017-10-07 |
| 2492 | SUN 2017-10-08 |

The query is NOT translating the day to a Monday but is instead reducing the difference by 3, so effectively calculating the number of full weeks between the current day and Sunday 4th January 1970.

A simplified version of your query would be:

SELECT TRUNC( ( SYSDATE - DATE '1970-01-04' ) / 7 ) AS Week
FROM   DUAL;

or a version which is more explanatory of the "magic" date:

SELECT TRUNC( ( SYSDATE - NEXT_DAY( DATE '1970-01-01', 'SUNDAY' ) ) / 7 ) AS Week
FROM   DUAL;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

1970-01-01 was a Thursday.

The purpose of subtracting three days is to move the start of the week to Monday.

As a note, the first day of the current millenium -- 2001-01-01 -- is conveniently a Monday. That makes it a convenient reference point.

Upvotes: 1

Related Questions