Returns a number that represents the week of the year in which a date value falls. Monday is considered the first day of a week.
Usage notes
To determine whether a week belongs to the current year or to the next year, find the year in which more than four days of the week fall. If the week belongs to the current year, it is considered the last week of the year. If the week belongs to the next year, it is considered the first week of the next year.
Syntax
bigint weekofyear (datetime <date>)Parameters
date: required. A value of the DATETIME type. The date value must be in the yyyy-mm-dd hh:mi:ss format. If the input value is of the STRING type and the MaxCompute V1.0 data type edition is used in your project, the input value is implicitly converted into the DATETIME type before calculation.
Return value
A value of the BIGINT type is returned. The return value varies based on the following rules:
If the value of date is not of the DATETIME or STRING type or the format does not meet the requirements, null is returned.
If the value of date is null, null is returned.
Sample data
This section provides sample source data for you to understand how to use date functions. In this topic, a table named mf_date_fun_t is created and data is inserted into the table. Sample statements:
create table if not exists mf_date_fun_t(
id int,
date1 date,
datetime1 datetime,
timestamp1 timestamp,
date2 date,
datetime2 datetime,
timestamp2 timestamp,
date3 string,
date4 bigint);
insert into mf_date_fun_t values
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);Query data from the mf_date_fun_t table. Sample statement:
select * from mf_date_fun_t;
-- The following result is returned:
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| id | date1 | datetime1 | timestamp1 | date2 | datetime2 | timestamp2 | date3 | date4 |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 1 | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780 |
| 2 | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781 |
| 3 | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782 |
| 4 | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783 |
| 5 | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784 |
| 6 | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785 |
| 7 | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786 |
| 8 | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787 |
| 9 | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788 |
| 10 | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-29 | 123456789 |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+Example: static data
-- The return value is 1. 20141229 is in year 2014, but most days of the week fall in year 2015. Therefore, the return value is 1, which indicates the first week of year 2015.
select weekofyear(to_date("20141229", "yyyymmdd"));
-- The return value is 1.
select weekofyear(to_date("20141231", "yyyymmdd"));
-- The return value is 53.
select weekofyear(to_date("20151229", "yyyymmdd"));
-- The return value is 48.
set odps.sql.type.system.odps2=false;
select weekofyear('2021-11-29 00:01:00');
-- The return value is null.
select weekofyear('20141231');
-- The return value is null.
select weekofyear(null);Example: table data
Calculate the week of the year in which each date in the datetime1 column falls. Data in Sample data is used in this example. Sample statement:
select datetime1, weekofyear(datetime1) as datetime1_weekofyear from mf_date_fun_t;The following result is returned:
+---------------------+----------------------+
| datetime1 | datetime1_weekofyear |
+---------------------+----------------------+
| 2021-11-29 00:01:00 | 48 |
| 2021-11-28 00:02:00 | 47 |
| 2021-11-27 00:03:00 | 47 |
| 2021-11-26 00:04:00 | 47 |
| 2021-11-25 00:05:00 | 47 |
| 2021-11-24 00:06:00 | 47 |
| 2021-11-23 00:07:00 | 47 |
| 2021-11-22 00:08:00 | 47 |
| 2021-11-21 00:09:00 | 46 |
| 2021-11-20 00:10:00 | 46 |
+---------------------+----------------------+Related functions
WEEKOFYEAR is a date function. For more information about functions related to date computing and conversion, see Date functions.