# Number of weeks for Oracle fetch date __oracle

Source: Internet
Author: User
Preface

Calculation criteria

Oracle supports two standards of time, one is Oracle's own standard, the other is ISO standard

1. Oralce Standard

1 every year January 1 as the first day of the year. (no matter what day of the week it is)

For example: 2013/01/01 is Tuesday, this day as the first day of 2013.

2 The calculation formula of week = Int (dayofyear+6)/7; DayOfYear is this day is the first days of the year

3) Weeks Range: 1-53
2. ISO Standard

1 every week is always starting from Monday, the end of Sunday

2 if January 1 is Friday, Saturday or Sunday, this week is the last week of the previous year, as most of the week is the year before

3 if January 1 is Monday, Tuesday, Wednesday or Thursday, this week is the first week of the new year, because most of this week is the year

4) time interval: 1-52 or 1-53

For example: For the 1998 and 1999 of January 1, 1998 is the first week, and the 1999 January 1 is counted as the last week of the previous year.

Table 3-7 The Week of the Year:example 1, January 1998

Mo Tu We Th Fr Sa Su ISO Week
- - - 1 2 3 4 1 ISO Week of 1998
5 6 7 8 9 10 11 2 ISO Week of 1998
12 13 14 15 16 17 18 3 ISO Week of 1998
19 20 21st 22 23 24 25 4 ISO Week of 1998
26 27 28 29 30 31 - 5 ISO Week of 1998

Table 3-8 The Week of the Year:example 2, January 1999

mo tu we fr sa su iso Week
- - - - 1 2 3 ISO Week of 1998
4 5 6 7 8 9 10 1 ISO Week of 1999
11 12 13 14 15 16 17 2 ISO Week of 1999
18 19 20 21st 22 23 24 3 ISO Week of 1999
25 26 27 28 29 30 31 4 ISO Week of 1999

acquired year in Oracle

1. Oralce Standard-YYYY

The way the Oralce standard gets the year is simple:

```Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' YYYY ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' YYYY ') from dual;
```

Are all back to 1997.

2. ISO standard-IYYY

```Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' iyyy ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' iyyy ') from dual;```
Return: 1997, 1998

Is it strange that the 1997/12/31 year was 1998.

In fact, the reason is very simple, according to the above criteria, January 1, 1998 is Thursday, then this week is the first week of the new year, because most of the week's time belongs to 1998. So 1997/12/31 also belonged to the first week of 1998, so it was 1998.

You might be thinking:

Is this because the string was converted to a date using YYYY-to_date (' 1997/12/31 ', ' yyyy/mm/dd '), if To_date (' 1997/12/31 ', ' iyyy/mm/dd ') is used, ' iyyy ' ) If you can. Unfortunately, Oracle does not support this usage.

Assuming that the day was 1997/12/31, using To_char (sysdate, ' iyyy '), it was found that the same was achieved by 1998.

To summarize: If you are converting from string to date, it is best to use YYYY if you are only taking the year of this date.

Oracle Get Week number

To get a day is the first week of the year, there are also two different criteria:

1. Oracle Standard-WW

This standard and algorithm looks silly. Int (dayofyear+6)/7

```Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' YYWW ') from dual;
Select To_char (to_date (' 1997/01/08 ', ' yyyy/mm/dd '), ' YYWW ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' YYWW ') from dual;```

Very simple; Return 9701, 9702,9753

However, this standard should be less used in general companies.

2. ISO standard-IW

```Select To_char (to_date (' 1997/01/01 ', ' yyyy/mm/dd '), ' IYIW ') from dual;
Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' IYIW ') from dual;```
return: 9701,9801

As noted above, 1997/12/31 is the first week of 1998.

Years and weeks to use the same standard, do not mix,

For example: YYIW--Oracle's year, ISO number of weeks

Iyww

Otherwise, you will get some wrong results.

`Select To_char (to_date (' 1997/12/31 ', ' yyyy/mm/dd '), ' YYIW ') from dual;`

return: 9701

It's a whole year short.

And this is a mistake you may not find in a year or so, a year from now, the discovery is back to a year ago ^^

Related Keywords:
Related Article

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.