Date functions - MaxCompute - Alibaba Cloud Documentation Center

This topic describes the syntax and parameters of the date functions that are supported by MaxCompute. This topic also provides examples on how to use date functions. You can select an appropriate date function to perform date calculation or conversion based on your business requirements.

Function	Description
ADD_MONTHS	Returns a date value that is obtained after a number of months are added to a specified date.
CURRENT_TIMESTAMP	Returns the current timestamp.
CURRENT_TIMEZONE	Returns the time zone of the current system.
DATE_ADD	Adds or subtracts a number of days to or from a date value based on the interval specified by delta. Only supports increasing or decreasing on a daily basis. The DATE_ADD function is the inverse of the `DATE_SUB` function.
DATEADD	Changes a date value based on the time unit specified by datepart and the interval specified by delta. Supports increasing or decreasing in units of years, months, days, hours, minutes, or seconds.
DATE_FORMAT	Converts a date value into a string in a specified format.
DATE_SUB	Adds or subtracts a number of days to or from a date value based on the interval specified by delta. The DATE_SUB function is the inverse of the `DATE_ADD` function.
DATEDIFF	Calculates the difference between two date values based on the time unit specified by datepart.
DATEPART	Returns a specified component of a date value based on the time unit specified by datepart.
DATETRUNC	Truncates a date value based on the time unit specified by datepart.
DAY	Returns the day in which a date value falls.
DAYOFMONTH	Returns the day component of a date value.
DAYOFWEEK	Returns the day of the week in which a date value falls.
DAYOFYEAR	Returns an integer that represents the sequential day of the year.
EXTRACT	Returns a specified component of a timestamp.
FROM_UNIXTIME	Converts a UNIX timestamp of the BIGINT type into a date value of the DATETIME type.
FROM_UTC_TIMESTAMP	Converts a UTC timestamp into a timestamp for a specified time zone.
GETDATE	Returns the current system time as a date value.
HOUR	Returns the hour component of a date value.
ISDATE	Determines whether a date string can be converted to a date value in a specified format.
LAST_DAY	Returns the last day of the month in which a date value falls.
LASTDAY	Returns the last day of the month in which a date value falls.
MINUTE	Returns the minute component of a date value.
MONTH	Returns the month in which a date value falls.
MONTHS_BETWEEN	Returns the number of months between specified date values.
NEXT_DAY	Returns the date of the first weekday that is later than a date value and matches the specified week.
NOW	Returns the current system date and time.
QUARTER	Returns the quarter in which a date value falls.
SECOND	Returns the second component of a date value.
TO_CHAR	Converts a date value to a string in a specified format.
TO_DATE	Converts a string into a date value in a specified format.
TO_MILLIS	Converts a date value into a UNIX timestamp that is accurate to the millisecond.
UNIX_TIMESTAMP	Converts a date value to a UNIX timestamp that is an integer.
WEEKDAY	Returns a number that represents the day of the week in which a date value falls.
WEEKOFYEAR	Returns a number that represents the week of the year in which a date value falls.
YEAR	Returns the year in which a date value falls.

Precautions

MaxCompute V2.0 provides additional functions. If the functions that you use involve new data types that are supported in the MaxCompute V2.0 data type edition, you must execute the SET statement to enable the MaxCompute V2.0 data type edition. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.

Session level: To use the MaxCompute V2.0 data type edition, you must add set odps.sql.type.system.odps2=true; before the SQL statement that you want to execute, and commit and execute them together.
Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. The configuration takes effect after 10 to 15 minutes. To enable the MaxCompute V2.0 data type edition at the project level, run the following command:
```
setproject odps.sql.type.system.odps2=true;
```
For more information about setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.

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;

+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 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  |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+

ADD_MONTHS

Syntax

string add_months(date|datetime|timestamp|string <startdate>, int <num_months>)

Description
Returns a date value that is obtained after the number of months specified by num_months is added to startdate. This function is an additional function of MaxCompute V2.0.
Parameters
- startdate: required. A value of the DATE, DATETIME, TIMESTAMP, or STRING type. The value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss.ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
- num_months: required. A value of the INT type.
Return value
A value of the STRING type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
- If the value of startdate is not of the DATE, DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
- If the value of startdate is null, an error is returned.
- If the value of num_months is null, null is returned.

Examples

Examples of static data

-- The return value is 2017-05-14. 
select add_months('2017-02-14',3);

-- The return value is 0017-05-14. 
select add_months('17-2-14',3);

-- The return value is 2017-05-14. 
select add_months('2017-02-14 21:30:00',3);

-- The return value is null. 
select add_months('20170214',3);

-- The return value is null. 
select add_months('2017-02-14 21:30:00',null);

Examples of table data

Convert date values in the date1, datetime1, timestamp1, and date3 columns into UNIX timestamps that are integers. Data in Sample data is used in this example. Sample statements:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, add_months(date1,1) as date1_add_months, datetime1, add_months(datetime1, 2) as datetime1_add_months, timestamp1, add_months(timestamp1,3) as timestamp1_add_months, date3, add_months(date3,4) as date3_add_months from mf_date_fun_t;

The following result is returned:

+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+
| date1      | date1_add_months | datetime1           | datetime1_add_months | timestamp1                    | timestamp1_add_months | date3      | date3_add_months |
+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+
| 2021-11-29 | 2021-12-29       | 2021-11-29 00:01:00 | 2022-01-29           | 2021-01-11 00:00:00.123456789 | 2021-04-11            | 2021-11-20 | 2022-03-20       |
| 2021-11-28 | 2021-12-28       | 2021-11-28 00:02:00 | 2022-01-28           | 2021-02-11 00:00:00.123456789 | 2021-05-11            | 2021-11-21 | 2022-03-21       |
| 2021-11-27 | 2021-12-27       | 2021-11-27 00:03:00 | 2022-01-27           | 2021-03-11 00:00:00.123456789 | 2021-06-11            | 2021-11-22 | 2022-03-22       |
| 2021-11-26 | 2021-12-26       | 2021-11-26 00:04:00 | 2022-01-26           | 2021-04-11 00:00:00.123456789 | 2021-07-11            | 2021-11-23 | 2022-03-23       |
| 2021-11-25 | 2021-12-25       | 2021-11-25 00:05:00 | 2022-01-25           | 2021-05-11 00:00:00.123456789 | 2021-08-11            | 2021-11-24 | 2022-03-24       |
| 2021-11-24 | 2021-12-24       | 2021-11-24 00:06:00 | 2022-01-24           | 2021-06-11 00:00:00.123456789 | 2021-09-11            | 2021-11-25 | 2022-03-25       |
| 2021-11-23 | 2021-12-23       | 2021-11-23 00:07:00 | 2022-01-23           | 2021-07-11 00:00:00.123456789 | 2021-10-11            | 2021-11-26 | 2022-03-26       |
| 2021-11-22 | 2021-12-22       | 2021-11-22 00:08:00 | 2022-01-22           | 2021-08-11 00:00:00.123456789 | 2021-11-11            | 2021-11-27 | 2022-03-27       |
| 2021-11-21 | 2021-12-21       | 2021-11-21 00:09:00 | 2022-01-21           | 2021-09-11 00:00:00.123456789 | 2021-12-11            | 2021-11-28 | 2022-03-28       |
| 2021-11-20 | 2021-12-20       | 2021-11-20 00:10:00 | 2022-01-20           | 2021-10-11 00:00:00.123456789 | 2022-01-11            | 2021-11-29 | 2022-03-29       |
+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+

CURRENT_TIMESTAMP

Syntax
```
timestamp current_timestamp()
```
Description
Returns the current timestamp. The return value is not fixed. This function is an additional function of MaxCompute V2.0.
Return value
A value of the TIMESTAMP type is returned.

Examples

-- The return value is '2017-08-03 11:50:30.661'. 
set odps.sql.type.system.odps2=true;
select current_timestamp();

CURRENT_TIMEZONE

Syntax
```
string current_timezone()
```
Description
Returns the time zone of the current system.
Return value
A value of the STRING type is returned.

Examples

-- The return value is Asia/Shanghai. 
select current_timezone();

DATE_ADD

Syntax

date date_add(date|timestamp|string <startdate>, bigint <delta>)

Description
Adds or subtracts a number of days that is specified by delta to or from a date value that is specified by startdate. To add or subtract a number of days to or from the current time, you can use this function with the GETDATE function.
The logic of this function is opposite to that of the DATE_SUB function.
Parameters
- startdate: required. The start date. A value of the DATE, DATETIME, or STRING type is supported.
  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 DATE type before calculation. The input value of the STRING type must include at least the 'yyyy-mm-dd' part, such as '2019-12-27'.
- delta: required. The number of days that you want to add or subtract. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, a number of days is added to the start date. If the value of delta is less than 0, a number of days is subtracted from the start date. If the value of delta is 0, the date value remains unchanged.
Return value
A value of the DATE type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
- If the value of startdate is not of the DATE, DATETIME, or STRING type, an error is returned.
- If the value of startdate is null, an error is returned.
- If the value of delta is null, null is returned.

Examples

Examples of static data

-- The return value is 2005-03-01. After one day is added, the result is beyond the last day of February. The first day of March is returned. 
select date_add(datetime '2005-02-28 00:00:00', 1);

-- The return value is 2005-02-27. One day is subtracted. 
select date_add(date '2005-02-28', -1);

-- The return value is 2005-03-20. 
set odps.sql.type.system.odps2=false; 
select date_add('2005-02-28 00:00:00', 20);

-- If the current time is 2020-11-17 16:31:44, the return value is 2020-11-16. 
select date_add(getdate(),-1);

-- The return value is null. 
select date_add('2005-02-28 00:00:00', null);

Examples of table data

Change date values in the date1, datetime1, and timestamp1 columns based on the number of days specified by delta. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, date_add(date1,1) as date1_date_add, datetime1, date_add(datetime1,-1) as datetime1_date_add, timestamp1, date_add(timestamp1,0) as timestamp1_date_add from mf_date_fun_t;

The following result is returned:

+------------+----------------+---------------------+-------------------+-------------------------------+--------------------+
| date1      | date1_date_add | datetime1           | datetime1_date_add| timestamp1                    | timestamp1_date_add|
+------------+----------------+---------------------+-------------------+-------------------------------+--------------------+
| 2021-11-29 | 2021-11-30     | 2021-11-29 00:01:00 | 2021-11-28        | 2021-01-11 00:00:00.123456789 | 2021-01-11         |
| 2021-11-28 | 2021-11-29     | 2021-11-28 00:02:00 | 2021-11-27        | 2021-02-11 00:00:00.123456789 | 2021-02-11         |
| 2021-11-27 | 2021-11-28     | 2021-11-27 00:03:00 | 2021-11-26        | 2021-03-11 00:00:00.123456789 | 2021-03-11         |
| 2021-11-26 | 2021-11-27     | 2021-11-26 00:04:00 | 2021-11-25        | 2021-04-11 00:00:00.123456789 | 2021-04-11         |
| 2021-11-25 | 2021-11-26     | 2021-11-25 00:05:00 | 2021-11-24        | 2021-05-11 00:00:00.123456789 | 2021-05-11         |
| 2021-11-24 | 2021-11-25     | 2021-11-24 00:06:00 | 2021-11-23        | 2021-06-11 00:00:00.123456789 | 2021-06-11         |
| 2021-11-23 | 2021-11-24     | 2021-11-23 00:07:00 | 2021-11-22        | 2021-07-11 00:00:00.123456789 | 2021-07-11         |
| 2021-11-22 | 2021-11-23     | 2021-11-22 00:08:00 | 2021-11-21        | 2021-08-11 00:00:00.123456789 | 2021-08-11         |
| 2021-11-21 | 2021-11-22     | 2021-11-21 00:09:00 | 2021-11-20        | 2021-09-11 00:00:00.123456789 | 2021-09-11         |
| 2021-11-20 | 2021-11-21     | 2021-11-20 00:10:00 | 2021-11-19        | 2021-10-11 00:00:00.123456789 | 2021-10-11         |
+------------+----------------+---------------------+-------------------+-------------------------------+--------------------+

DATEADD

Syntax

date|datetime dateadd(date|datetime|timestamp <date>, bigint <delta>, string <datepart>)

Description
Changes a date value based on the time unit specified by datepart and the interval specified by delta. To add or subtract an interval to or from the current time, you can use this function with the GETDATE function.
Parameters
- date: required. A date value of the DATE, DATETIME, or TIMESTAMP type.
  A value of the STRING type is implicitly converted into a value of the DATETIME type before calculation if the value format conforms to the DATETIME-type format yyyy-mm-dd hh:mi:ss, such as 2021-08-28 00:00:00, and the MaxCompute V1.0 data type edition is used.
- delta: required. The interval that you want to add to or subtract from the specified component of a date value. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, an interval is added to the date value. Otherwise, an interval is subtracted from the date value.
  If the input value is of the STRING or DOUBLE type, the value is implicitly converted into a value of the BIGINT type before calculation.
  Note
  - If you add or subtract the interval specified by delta based on the time unit specified by datepart, a carry or return at more significant date components may occur. The year, month, hour, minute, and second parts are computed according to the base-10, base-12, base-24, and base-60 numeral systems, respectively.
  - If the DATEADD function adds the interval specified by delta to the month component of a date value and this operation does not cause an overflow on the day component, retain the value of the day component. Otherwise, set the value of the day component to the last day of the specified month.
- datepart: required. The date part that you want to modify in the date value. The value is a constant of the STRING type. If the format of the input value is invalid or the input value is not a constant of the STRING type, an error is returned.
  The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates the year component of a date value. The value mm indicates the month component of a date value. The value dd indicates the day component of a date value. For more information about the rules for type conversions, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, or -hour.
Return value
A value of the DATE or DATETIME type is returned. The return value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. The return value varies based on the following rules:
- If the value of date is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
- If the value of date is null, an error is returned.
- If the value of delta or datepart is null, null is returned.

Examples

Examples of static data

Example 1: common use

-- The return value is 2005-03-01 00:00:00. After one day is added, the result is beyond the last day of February. The first day of March is returned. 
select dateadd(datetime '2005-02-28 00:00:00', 1, 'dd');

-- The return value is 2005-02-27 00:00:00. One day is subtracted. 
select dateadd(datetime '2005-02-28 00:00:00', -1, 'dd');

-- The return value is 2006-10-28 00:00:00. After 20 months are added, the month overflows, and the year value increases by 1. 
select dateadd(datetime '2005-02-28 00:00:00', 20, 'mm');

-- The return value is 2005-03-28 00:00:00. 
select dateadd(datetime '2005-02-28 00:00:00', 1, 'mm');

-- The return value is 2005-02-28 00:00:00. February in 2005 has only 28 days. Therefore, the last day of February is returned. 
select dateadd(datetime '2005-01-29 00:00:00', 1, 'mm');

-- The return value is 2005-02-28 00:00:00. 
select dateadd(datetime '2005-03-30 00:00:00', -1, 'mm');

-- The return value is 2005-03-18. 
select dateadd(date '2005-02-18', 1, 'mm');

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with the SQL statement. The return value is 2005-03-18 00:00:00.0. 
set odps.sql.type.system.odps2=true;
select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm');

-- If the current time is 2020-11-17 16:31:44, the return value is 2020-11-16 16:31:44. 
select dateadd(getdate(),-1,'dd');

-- The return value is null. 
select dateadd(date '2005-02-18', 1, null);

Example 2: use of DATEADD in which a value of the DATETIME type is expressed as a constant.
In MaxCompute SQL statements, a value of the DATETIME type cannot be directly expressed as a constant. The following statement uses an invalid expression of a value of the DATETIME type:
```
select dateadd(2005-03-30 00:00:00, -1, 'mm');
```
To describe a constant of the DATETIME type, use a valid expression of a value of the DATETIME type in the following statement:
```
-- Explicitly convert a constant of the STRING type into the DATETIME type. The return value is 2005-02-28 00:00:00. 
select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm');
```

Example 3: The input value is of the STRING type.

-- The input value is of the STRING type but does not conform to the DATETIME-type format. As a result, an error is returned. 
select dateadd('2021-08-27',1,'dd');

-- The input value is of the STRING type and conforms to the DATETIME-type format, and the MaxCompute V1.0 data type edition is used in your project. The return value is 2005-03-01 00:00:00. 
set odps.sql.type.system.odps2=false;
select dateadd('2005-02-28 00:00:00', 1, 'dd');

Examples of table data

Change date values in the date1, datetime1, and timestamp1 columns based on the time unit specified by datepart and the interval specified by delta. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, dateadd(date1,1,'dd') as date1_dateadd, datetime1, dateadd(datetime1,1,'mm') as datetime1_dateadd, timestamp1, dateadd(timestamp1,-1,'yyyy') as timestamp1_dateadd from mf_date_fun_t;

The following result is returned:

+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| date1      | date1_dateadd | datetime1           | datetime1_dateadd   | timestamp1                    | timestamp1_dateadd            |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+
| 2021-11-29 | 2021-11-30    | 2021-11-29 00:01:00 | 2021-12-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2020-01-11 00:00:00.123456789 |
| 2021-11-28 | 2021-11-29    | 2021-11-28 00:02:00 | 2021-12-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2020-02-11 00:00:00.123456789 |
| 2021-11-27 | 2021-11-28    | 2021-11-27 00:03:00 | 2021-12-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2020-03-11 00:00:00.123456789 |
| 2021-11-26 | 2021-11-27    | 2021-11-26 00:04:00 | 2021-12-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2020-04-11 00:00:00.123456789 |
| 2021-11-25 | 2021-11-26    | 2021-11-25 00:05:00 | 2021-12-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2020-05-11 00:00:00.123456789 |
| 2021-11-24 | 2021-11-25    | 2021-11-24 00:06:00 | 2021-12-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2020-06-11 00:00:00.123456789 |
| 2021-11-23 | 2021-11-24    | 2021-11-23 00:07:00 | 2021-12-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2020-07-11 00:00:00.123456789 |
| 2021-11-22 | 2021-11-23    | 2021-11-22 00:08:00 | 2021-12-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2020-08-11 00:00:00.123456789 |
| 2021-11-21 | 2021-11-22    | 2021-11-21 00:09:00 | 2021-12-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2020-09-11 00:00:00.123456789 |
| 2021-11-20 | 2021-11-21    | 2021-11-20 00:10:00 | 2021-12-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2020-10-11 00:00:00.123456789 |
+------------+---------------+---------------------+---------------------+-------------------------------+-------------------------------+

DATE_FORMAT

Syntax

string date_format(date|timestamp|string <date>, string <format>)

Description
Converts a date value into a string in a specified format.
Parameters
- date: required. The date value that you want to convert. The date value can be of the DATE, TIMESTAMP, or STRING type.
  - Date values of the DATE or STRING type are supported only when the Hive-compatible data type edition is enabled. You can run the set odps.sql.hive.compatible=true; command to enable the Hive-compatible data type edition.
  - If the date value is of the STRING type, only the following formats are supported:
    - 'yyyy-MM-dd', such as '2019-12-27'.
    - 'yyyy-MM-dd hh:mm:ss', such as '2019-12-27 12:23:10'.
    - 'yyyy-MM-dd hh:mm:ss.SSS', such as '2019-12-27 12:23:10.123'.
- format: required. A constant of the STRING type. This parameter specifies the date format. This parameter specifies the date format. Examples: yyyy-MM-dd hh:mm:ss:SSS and yyyy-MM-dd hh:mi:ss:SSS. format consists of the following components:
  - YYYY or yyyy: the year. yyyy represents the calendar year and YYYY represents the year of the week.
    Note
    The year of the week may be inconsistent with the actual year. Exercise caution when you use the year of the week format.
  - MM: the month
  - mm: the minute
  - dd: the day
  - HH: the hour that is expressed in the 24-hour clock
  - hh: the hour that is expressed in the 12-hour clock
  - mi: the minute
  - ss: the second
  - SSS: the millisecond
  Important
  - If the Hive-compatible data type edition is disabled, both HH and hh indicate that a 24-hour clock is used. In this case, the date format must be yyyy-MM-dd hh:mi:ss. If yyyy-MM-dd hh:mm:ss is used, the mm component takes the same value as the MM component.
  - If the Hive-compatible data type edition is enabled, HH indicates that a 24-hour clock is used, and hh indicates that a 12-hour clock is used. In this case, the date format must be yyyy-MM-dd hh:mm:ss. If yyyy-MM-dd hh:mi:ss is used, null is returned.
Return value
A value of the STRING type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATE or TIMESTAMP type, null is returned.
- If the value of date is null, an error is returned.
- If the value of format is null, null is returned.

Examples

Examples of static data

-- Enable the Hive-compatible data type edition. Commit the following SET statement together with the SQL statement. 
set odps.sql.hive.compatible=true;

-- If the current time is 2022-04-24 15:49, the return value is 2022-04-24 03:49:01.902. 
select date_format(from_utc_timestamp(current_timestamp(), 'UTC'),'yyyy-MM-dd hh:mm:ss.SSS');

-- The return value is 2022-04-24. 
select date_format('2022-04-24','yyyy-MM-dd');

Examples of table data

Convert date values in the datetime1 and timestamp1 columns into strings in a specified format. Data in Sample data is used in this example. Sample statements:

-- Enable the Hive-compatible data type edition and the MaxCompute V2.0 data type edition. Commit the following SET statements together with the SQL statement. 
set odps.sql.hive.compatible=true;
set odps.sql.type.system.odps2=true;
select datetime1, timestamp1, date_format(datetime1,'yyyy/MM/dd'),
       date_format(datetime1,'yyyy/MM/dd HH:mm:ss'),
       date_format(timestamp1,'yyyy/MM/dd HH:mm:ss')
from mf_date_fun_t;

The following result is returned:

+------------+------------+-----+-----+-----+
| datetime1  | timestamp1 | _c2 | _c3 | _c4 |
+------------+------------+-----+-----+-----+
| 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021/11/29 | 2021/11/29 00:01:00 | 2021/01/11 00:00:00 |
| 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021/11/28 | 2021/11/28 00:02:00 | 2021/02/11 00:00:00 |
| 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021/11/27 | 2021/11/27 00:03:00 | 2021/03/11 00:00:00 |
| 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021/11/26 | 2021/11/26 00:04:00 | 2021/04/11 00:00:00 |
| 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021/11/25 | 2021/11/25 00:05:00 | 2021/05/11 00:00:00 |
| 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021/11/24 | 2021/11/24 00:06:00 | 2021/06/11 00:00:00 |
| 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021/11/23 | 2021/11/23 00:07:00 | 2021/07/11 00:00:00 |
| 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021/11/22 | 2021/11/22 00:08:00 | 2021/08/11 00:00:00 |
| 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021/11/21 | 2021/11/21 00:09:00 | 2021/09/11 00:00:00 |
| 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021/11/20 | 2021/11/20 00:10:00 | 2021/10/11 00:00:00 |
+------------+------------+-----+-----+-----+

DATE_SUB

Syntax

date date_sub(date|timestamp|string <startdate>, bigint <delta>)

Description
Adds or subtracts a number of days that is specified by delta to or from a date value that is specified by startdate. To add or subtract a number of days to or from the current time, you can use this function with the GETDATE function.
The logic of this function is opposite to that of the DATE_ADD function.
Parameters
- startdate: required. The start date. A value of the DATE, DATETIME, or STRING type is supported.
  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 DATE type before calculation. The input value of the STRING type must include at least the 'yyyy-mm-dd' part, such as '2019-12-27'.
- delta: required. The number of days that you want to add or subtract. The value of this parameter must be of the BIGINT type. If the value of delta is greater than 0, a number of days are subtracted from the start date. If the value of delta is less than 0, a number of days are added to the start date. If the value of delta is 0, the date value remains unchanged.
Return value
A value of the DATE type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
- If the value of startdate is not of the DATE, DATETIME, or STRING type, an error is returned.
- If the value of startdate is null, an error is returned.
- If the value of delta is null, null is returned.

Examples

Examples of static data

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;

-- The return value is 2005-02-28. One day is subtracted. The last day of February is returned. 
select date_sub(datetime '2005-03-01 00:00:00', 1);

-- The return value is 2005-03-01. One day is added. 
select date_sub(date '2005-02-28', -1);

-- The return value is 2005-02-27. Two days are subtracted. 
set odps.sql.type.system.odps2=false; 
select date_sub('2005-03-01 00:00:00', 2);

-- If the current time is 2021-09-10 16:31:44, the return value is 2021-09-09. 
select date_sub(getdate(),1);

-- The return value is null. 
select date_sub('2005-03-01 00:00:00', null);

Examples of table data

Change date values in the date1, datetime1, and timestamp1 columns based on the number of days specified by delta. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, date_sub(date1,1) as date1_date_sub, datetime1, date_sub(datetime1,-1) as datetime1_date_sub, timestamp1, date_sub(timestamp1,0) as timestamp1_date_sub from mf_date_fun_t;

The following result is returned:

+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| date1      | date1_date_sub | datetime1           | datetime1_date_sub | timestamp1                    | timestamp1_date_sub |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+
| 2021-11-29 | 2021-11-28     | 2021-11-29 00:01:00 | 2021-11-30         | 2021-01-11 00:00:00.123456789 | 2021-01-11          |
| 2021-11-28 | 2021-11-27     | 2021-11-28 00:02:00 | 2021-11-29         | 2021-02-11 00:00:00.123456789 | 2021-02-11          |
| 2021-11-27 | 2021-11-26     | 2021-11-27 00:03:00 | 2021-11-28         | 2021-03-11 00:00:00.123456789 | 2021-03-11          |
| 2021-11-26 | 2021-11-25     | 2021-11-26 00:04:00 | 2021-11-27         | 2021-04-11 00:00:00.123456789 | 2021-04-11          |
| 2021-11-25 | 2021-11-24     | 2021-11-25 00:05:00 | 2021-11-26         | 2021-05-11 00:00:00.123456789 | 2021-05-11          |
| 2021-11-24 | 2021-11-23     | 2021-11-24 00:06:00 | 2021-11-25         | 2021-06-11 00:00:00.123456789 | 2021-06-11          |
| 2021-11-23 | 2021-11-22     | 2021-11-23 00:07:00 | 2021-11-24         | 2021-07-11 00:00:00.123456789 | 2021-07-11          |
| 2021-11-22 | 2021-11-21     | 2021-11-22 00:08:00 | 2021-11-23         | 2021-08-11 00:00:00.123456789 | 2021-08-11          |
| 2021-11-21 | 2021-11-20     | 2021-11-21 00:09:00 | 2021-11-22         | 2021-09-11 00:00:00.123456789 | 2021-09-11          |
| 2021-11-20 | 2021-11-19     | 2021-11-20 00:10:00 | 2021-11-21         | 2021-10-11 00:00:00.123456789 | 2021-10-11          |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+

DATEDIFF

Syntax

bigint datediff(date|datetime|timestamp <date1>, date|datetime|timestamp <date2>, string <datepart>)

Description
Calculates the difference between date1 and date2. The difference is measured in the time unit specified by datepart.
Parameters
- date1 and date2: required. The minuend and subtrahend, which are of the DATE, DATETIME, or TIMESTAMP type. If the input values are of the STRING type and the MaxCompute V1.0 data type edition is used in your project, the input values are implicitly converted into the DATETIME type before calculation.
  Note
  Only MaxCompute V2.0 data type edition supports the TIMESTAMP type. For more information, see MaxCompute V2.0 data type edition.
- datepart: optional. The time unit, which is a constant of the STRING type.
  If you enable the MaxCompute V2.0 data type edition, you can leave datepart empty. Default value: day. For more information about the MaxCompute V2.0 data type edition, see Data type editions. The EDTF is also supported, such as -year, -month, -mon, -day, or -hour.
  Note
  This function omits the lower unit based on the time unit specified by datepart and then calculates the result.
Return value
A value of the BIGINT type is returned. The return value varies based on the following rules:
- If the value of date1 or date2 is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
- If date1 is earlier than date2, a negative value is returned.
- If the value of date1 or date2 is null, null is returned.
- If the value of datepart is null, null is returned.

Examples

Examples of static data

Example 1

SET odps.sql.type.system.odps2=true;
SELECT DATEDIFF(TIMESTAMP '2006-01-01 00:00:00', TIMESTAMP '2005-12-31 23:59:59', 'dd');

The following result is returned:

+------------+
| _c0        |
+------------+
| 1          |
+------------+

Example 2

SET odps.sql.type.system.odps2=true;
SELECT DATEDIFF(TIMESTAMP '2006-01-01 00:00:00', TIMESTAMP '2005-12-31 23:59:59', 'mm');

The following result is returned:

+------------+
| _c0        |
+------------+
| 1          |
+------------+

Example 3

SELECT  DATEDIFF(DATETIME '2013-05-31 13:00:00', DATETIME '2013-05-31 12:30:00','ss');

The following result is returned:

+------------+
| _c0        |
+------------+
| 1800       |
+------------+

Example 4

SET odps.sql.type.system.odps2 = false;
SELECT  DATEDIFF('2013-05-31 13:00:00','2013-05-31 12:30:00','mi');

The following result is returned:

+------------+
| _c0        |
+------------+
| 30         |
+------------+

Example 5

--含毫秒的日期不属于标准DATETIME式样，不能直接隐式转换，此处需进行显示转换
SELECT  DATEDIFF(TO_DATE('2018-06-04 19:33:23.250','yyyy-mm-dd hh:mi:ss.ff3'),TO_DATE('2018-06-04 19:33:23.234','yyyy-mm-dd hh:mi:ss.ff3'),'ff3');

The following result is returned:

+------------+
| _c0        |
+------------+
| 16         |
+------------+

Example 6

SET odps.sql.type.system.odps2=true;
SELECT  DATEDIFF(DATE '2013-05-21',DATE '2013-05-10',NULL);

The following result is returned:

+------------+
| _c0        |
+------------+
| NULL       |
+------------+

Examples of table data

Calculate the differences between the values in the date1 and date2 columns, between the values in the datetime1 and datetime2 columns, and between the values in the timestamp1 and timestamp2 columns. The differences are measured in the specified time unit. Data in Sample data is used in this example. Sample statements:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
SET odps.sql.type.system.odps2 = true;

SELECT  date1
        ,date2
        ,DATEDIFF(date1,date2,'dd') AS date1_date2_datediff
        ,datetime1
        ,datetime2
        ,DATEDIFF(datetime1,datetime2,'dd') AS datetime1_datetime2_datediff
        ,timestamp1
        ,timestamp2
        ,DATEDIFF(timestamp1,timestamp2,'mm') AS timestamp1_timestamp2_datediff
FROM    mf_date_fun_t;

The following result is returned:

+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+
| date1      | date2      | date1_date2_datediff | datetime1           | datetime2           | datetime1_datetime2_datediff | timestamp1                    | timestamp2                    | timestamp1_timestamp2_datediff |
+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+
| 2021-11-29 | 2021-10-29 | 31                   | 2021-11-29 00:01:00 | 2021-10-29 00:00:00 | 31                           | 2021-01-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -9                             |
| 2021-11-28 | 2021-10-29 | 30                   | 2021-11-28 00:02:00 | 2021-10-29 00:00:00 | 30                           | 2021-02-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -8                             |
| 2021-11-27 | 2021-10-29 | 29                   | 2021-11-27 00:03:00 | 2021-10-29 00:00:00 | 29                           | 2021-03-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -7                             |
| 2021-11-26 | 2021-10-29 | 28                   | 2021-11-26 00:04:00 | 2021-10-29 00:00:00 | 28                           | 2021-04-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -6                             |
| 2021-11-25 | 2021-10-29 | 27                   | 2021-11-25 00:05:00 | 2021-10-29 00:00:00 | 27                           | 2021-05-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -5                             |
| 2021-11-24 | 2021-10-29 | 26                   | 2021-11-24 00:06:00 | 2021-10-29 00:00:00 | 26                           | 2021-06-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -4                             |
| 2021-11-23 | 2021-10-29 | 25                   | 2021-11-23 00:07:00 | 2021-10-29 00:00:00 | 25                           | 2021-07-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -3                             |
| 2021-11-22 | 2021-10-29 | 24                   | 2021-11-22 00:08:00 | 2021-10-29 00:00:00 | 24                           | 2021-08-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -2                             |
| 2021-11-21 | 2021-10-29 | 23                   | 2021-11-21 00:09:00 | 2021-10-29 00:00:00 | 23                           | 2021-09-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -1                             |
| 2021-11-20 | 2021-10-29 | 22                   | 2021-11-20 00:10:00 | 2021-10-29 00:00:00 | 22                           | 2021-10-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | 0                              |
+------------+------------+----------------------+---------------------+---------------------+------------------------------+-------------------------------+-------------------------------+--------------------------------+

DATEPART

Syntax

bigint datepart(date|datetime|timestamp <date>, string <datepart>)

Description
Returns a specified component of a date value based on the time unit specified by datepart.
Parameters
- date: required. A value of the DATE, DATETIME, or TIMESTAMP type. 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.
- datepart: required. A constant of the STRING type. This parameter supports EDTF.
  The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates the year component of a date value. The value mm indicates the month component of a date value. The value dd indicates the day component of a date value. For more information about the rules for type conversions, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, or -hour.
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 DATE, DATETIME, or TIMESTAMP type, an error is returned.
- If the value of date is null, an error is returned.
- If the value of datepart is null, null is returned.

Examples

Examples of static data

-- The return value is 2013. 
select datepart(datetime'2013-06-08 01:10:00', 'yyyy'); 

-- The return value is 6. 
select datepart(datetime'2013-06-08 01:10:00', 'mm');

-- The return value is 2013. 
select datepart(date '2013-06-08', 'yyyy');

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with the SQL statement. The return value is 2013. 
set odps.sql.type.system.odps2=true;
select datepart(timestamp '2013-06-08 01:10:00', 'yyyy');

-- The return value is 2013. 
set odps.sql.type.system.odps2=false;
select datepart('2013-06-08 01:10:00', 'yyyy');

-- The return value is null. 
select datepart(date '2013-06-08', null);

Examples of table data

Extract date values from the date1, datetime1, and timestamp1 columns based on the time unit specified by datepart. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, datepart(date1,'yyyy') as date1_datepart, datetime1, datepart(datetime1,'dd') as datetime1_datepart, timestamp1, datepart(timestamp1,'mm') as timestamp1_datepart from mf_date_fun_t;

The following result is returned:

+------------+----------------+---------------------+-------------------------+-------------------------------+---------------------+
| date1      | date1_datepart | datetime1           | datetime1_datepart      | timestamp1                    | timestamp1_datepart |
+------------+----------------+---------------------+-------------------------+-------------------------------+---------------------+
| 2021-11-29 | 2021           | 2021-11-29 00:01:00 | 29                      | 2021-01-11 00:00:00.123456789 | 1                   |
| 2021-11-28 | 2021           | 2021-11-28 00:02:00 | 28                      | 2021-02-11 00:00:00.123456789 | 2                   |
| 2021-11-27 | 2021           | 2021-11-27 00:03:00 | 27                      | 2021-03-11 00:00:00.123456789 | 3                   |
| 2021-11-26 | 2021           | 2021-11-26 00:04:00 | 26                      | 2021-04-11 00:00:00.123456789 | 4                   |
| 2021-11-25 | 2021           | 2021-11-25 00:05:00 | 25                      | 2021-05-11 00:00:00.123456789 | 5                   |
| 2021-11-24 | 2021           | 2021-11-24 00:06:00 | 24                      | 2021-06-11 00:00:00.123456789 | 6                   |
| 2021-11-23 | 2021           | 2021-11-23 00:07:00 | 23                      | 2021-07-11 00:00:00.123456789 | 7                   |
| 2021-11-22 | 2021           | 2021-11-22 00:08:00 | 22                      | 2021-08-11 00:00:00.123456789 | 8                   |
| 2021-11-21 | 2021           | 2021-11-21 00:09:00 | 21                      | 2021-09-11 00:00:00.123456789 | 9                   |
| 2021-11-20 | 2021           | 2021-11-20 00:10:00 | 20                      | 2021-10-11 00:00:00.123456789 | 10                  |
+------------+----------------+---------------------+-------------------------+-------------------------------+---------------------+

DATETRUNC

Syntax

date|datetime datetrunc (date|datetime|timestamp <date>, string <datepart>)

Description
Truncates a date value based on the time unit specified by datepart.
Parameters
- date: required. A value of the DATE, DATETIME, or TIMESTAMP type. 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.
- datepart: required. A constant of the STRING type. This parameter supports EDTF.
  The value of this parameter is specified in compliance with the rules of conversions between the STRING and DATETIME types. The value yyyy indicates the year component of a date value. The value mm indicates the month component of a date value. The value dd indicates the day component of a date value. For more information about the rules for type conversions, see Type conversions. The Extended Date/Time Format (EDTF) is also supported, such as -year, -month, -mon, -day, or -hour.
Return value
A value of the DATE or DATETIME type is returned. The return value is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format. The return value varies based on the following rules:
- If the value of date is not of the DATE, DATETIME, or TIMESTAMP type, an error is returned.
- If the value of date is null, an error is returned.
- If the value of datepart is null, null is returned.

Examples

Examples of static data

-- The return value is 2011-01-01 00:00:00. 
select datetrunc(datetime'2011-12-07 16:28:46', 'yyyy');

-- The return value is 2011-12-01 00:00:00. 
select datetrunc(datetime'2011-12-07 16:28:46', 'month');

-- The return value is 2011-12-07 00:00:00. 
select datetrunc(datetime'2011-12-07 16:28:46', 'DD');

-- The return value is 2011-01-01. 
select datetrunc(date '2011-12-07', 'yyyy');

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with the SQL statement. The return value is 2011-01-01 00:00:00.0. 
set odps.sql.type.system.odps2=true;
select datetrunc(timestamp '2011-12-07 16:28:46', 'yyyy');

-- The return value is 2011-01-01 00:00:00.0. 
set odps.sql.type.system.odps2=false;
select datetrunc('2011-12-07 16:28:46', 'yyyy');

-- The return value is null. 
select datetrunc(date '2011-12-07', null);

Examples of table data

Truncate date values in the date1, datetime1, and timestamp1 columns based on the time unit specified by datepart. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, datetrunc(date1,'yyyy') as date1_datetrunc, datetime1, datetrunc(datetime1,'dd') as datetime1_datetrunc, timestamp1, datetrunc(timestamp1,'mm') as timestamp1_datetrunc from mf_date_fun_t;

The following result is returned:

+------------+-----------------+---------------------+---------------------+-------------------------------+----------------------+
| date1      | date1_datetrunc | datetime1           | datetime1_datetrunc | timestamp1                    | timestamp1_datetrunc |
+------------+-----------------+---------------------+---------------------+-------------------------------+----------------------+
| 2021-11-29 | 2021-01-01      | 2021-11-29 00:01:00 | 2021-11-29 00:00:00 | 2021-01-11 00:00:00.123456789 | 2021-01-01 00:00:00  |
| 2021-11-28 | 2021-01-01      | 2021-11-28 00:02:00 | 2021-11-28 00:00:00 | 2021-02-11 00:00:00.123456789 | 2021-02-01 00:00:00  |
| 2021-11-27 | 2021-01-01      | 2021-11-27 00:03:00 | 2021-11-27 00:00:00 | 2021-03-11 00:00:00.123456789 | 2021-03-01 00:00:00  |
| 2021-11-26 | 2021-01-01      | 2021-11-26 00:04:00 | 2021-11-26 00:00:00 | 2021-04-11 00:00:00.123456789 | 2021-04-01 00:00:00  |
| 2021-11-25 | 2021-01-01      | 2021-11-25 00:05:00 | 2021-11-25 00:00:00 | 2021-05-11 00:00:00.123456789 | 2021-05-01 00:00:00  |
| 2021-11-24 | 2021-01-01      | 2021-11-24 00:06:00 | 2021-11-24 00:00:00 | 2021-06-11 00:00:00.123456789 | 2021-06-01 00:00:00  |
| 2021-11-23 | 2021-01-01      | 2021-11-23 00:07:00 | 2021-11-23 00:00:00 | 2021-07-11 00:00:00.123456789 | 2021-07-01 00:00:00  |
| 2021-11-22 | 2021-01-01      | 2021-11-22 00:08:00 | 2021-11-22 00:00:00 | 2021-08-11 00:00:00.123456789 | 2021-08-01 00:00:00  |
| 2021-11-21 | 2021-01-01      | 2021-11-21 00:09:00 | 2021-11-21 00:00:00 | 2021-09-11 00:00:00.123456789 | 2021-09-01 00:00:00  |
| 2021-11-20 | 2021-01-01      | 2021-11-20 00:10:00 | 2021-11-20 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-10-01 00:00:00  |
+------------+-----------------+---------------------+---------------------+-------------------------------+----------------------+

DAY

Syntax

int day(datetime|timestamp|date|string <date>)

Description
Returns the day in which a date value falls. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The value 1 is returned. 
select day('2014-09-01');

-- The return value is null. 
select day('20140901');


-- The return value is null. 
select day(null);

Examples of table data

Obtain the day in which each date value in the date1, datetime1, timestamp1, and date3 columns falls. Data in Sample data is used in this example. Sample statements:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, day(date1) as date1_day, datetime1, day(datetime1) as datetime1_day, timestamp1, day(timestamp1) as timestamp1_day, date3, day(date3) as date3_day from mf_date_fun_t;

The following result is returned:

+------------+-----------+---------------------+---------------+-------------------------------+----------------+------------+-----------+
| date1      | date1_day | datetime1           | datetime1_day | timestamp1                    | timestamp1_day | date3      | date3_day |
+------------+-----------+---------------------+---------------+-------------------------------+----------------+------------+-----------+
| 2021-11-29 | 29        | 2021-11-29 00:01:00 | 29            | 2021-01-11 00:00:00.123456789 | 11             | 2021-11-20 | 20        |
| 2021-11-28 | 28        | 2021-11-28 00:02:00 | 28            | 2021-02-11 00:00:00.123456789 | 11             | 2021-11-21 | 21        |
| 2021-11-27 | 27        | 2021-11-27 00:03:00 | 27            | 2021-03-11 00:00:00.123456789 | 11             | 2021-11-22 | 22        |
| 2021-11-26 | 26        | 2021-11-26 00:04:00 | 26            | 2021-04-11 00:00:00.123456789 | 11             | 2021-11-23 | 23        |
| 2021-11-25 | 25        | 2021-11-25 00:05:00 | 25            | 2021-05-11 00:00:00.123456789 | 11             | 2021-11-24 | 24        |
| 2021-11-24 | 24        | 2021-11-24 00:06:00 | 24            | 2021-06-11 00:00:00.123456789 | 11             | 2021-11-25 | 25        |
| 2021-11-23 | 23        | 2021-11-23 00:07:00 | 23            | 2021-07-11 00:00:00.123456789 | 11             | 2021-11-26 | 26        |
| 2021-11-22 | 22        | 2021-11-22 00:08:00 | 22            | 2021-08-11 00:00:00.123456789 | 11             | 2021-11-27 | 27        |
| 2021-11-21 | 21        | 2021-11-21 00:09:00 | 21            | 2021-09-11 00:00:00.123456789 | 11             | 2021-11-28 | 28        |
| 2021-11-20 | 20        | 2021-11-20 00:10:00 | 20            | 2021-10-11 00:00:00.123456789 | 11             | 2021-11-29 | 29        |
+------------+-----------+---------------------+---------------+-------------------------------+----------------+------------+-----------+

DAYOFMONTH

Syntax

int dayofmonth(datetime|timestamp|date|string <date>)

Description
Returns the day component of a date value. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 1. 
select dayofmonth('2014-09-01');

-- The return value is null. 
select dayofmonth('20140901');

-- The return value is null. 
select dayofmonth(null);

Examples of table data

Obtain the day components of date values in the date1, datetime1, timestamp1, and date3 columns. Data in Sample data is used in this example. Sample statements:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, dayofmonth(date1) as date1_dayofmonth, datetime1, dayofmonth(datetime1) as datetime1_dayofmonth, timestamp1, dayofmonth(timestamp1) as timestamp1_dayofmonth, date3, dayofmonth(date3) as date3_dayofmonth from mf_date_fun_t;

The following result is returned:

+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+
| date1      | date1_dayofmonth | datetime1           | datetime1_dayofmonth | timestamp1                    | timestamp1_dayofmonth | date3      | date3_dayofmonth |
+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+
| 2021-11-29 | 29               | 2021-11-29 00:01:00 | 29                   | 2021-01-11 00:00:00.123456789 | 11                    | 2021-11-20 | 20               |
| 2021-11-28 | 28               | 2021-11-28 00:02:00 | 28                   | 2021-02-11 00:00:00.123456789 | 11                    | 2021-11-21 | 21               |
| 2021-11-27 | 27               | 2021-11-27 00:03:00 | 27                   | 2021-03-11 00:00:00.123456789 | 11                    | 2021-11-22 | 22               |
| 2021-11-26 | 26               | 2021-11-26 00:04:00 | 26                   | 2021-04-11 00:00:00.123456789 | 11                    | 2021-11-23 | 23               |
| 2021-11-25 | 25               | 2021-11-25 00:05:00 | 25                   | 2021-05-11 00:00:00.123456789 | 11                    | 2021-11-24 | 24               |
| 2021-11-24 | 24               | 2021-11-24 00:06:00 | 24                   | 2021-06-11 00:00:00.123456789 | 11                    | 2021-11-25 | 25               |
| 2021-11-23 | 23               | 2021-11-23 00:07:00 | 23                   | 2021-07-11 00:00:00.123456789 | 11                    | 2021-11-26 | 26               |
| 2021-11-22 | 22               | 2021-11-22 00:08:00 | 22                   | 2021-08-11 00:00:00.123456789 | 11                    | 2021-11-27 | 27               |
| 2021-11-21 | 21               | 2021-11-21 00:09:00 | 21                   | 2021-09-11 00:00:00.123456789 | 11                    | 2021-11-28 | 28               |
| 2021-11-20 | 20               | 2021-11-20 00:10:00 | 20                   | 2021-10-11 00:00:00.123456789 | 11                    | 2021-11-29 | 29               |
+------------+------------------+---------------------+----------------------+-------------------------------+-----------------------+------------+------------------+

DAYOFWEEK

Syntax

int dayofweek(datetime|timestamp|date|string <date>)

Description
Returns the day of the week in which a date value falls. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.
- The return value ranges from 1 to 7. The value 1 indicates Sunday, the value 2 indicates Monday, and the same rule applies to other values.

Examples

-- The return value is 5. The return value indicates Thursday. 
SELECT dayofweek('2009-07-30');

DAYOFYEAR

Syntax

int dayofyear(datetime|timestamp|date|string <date>)

Description
Returns an integer that represents the sequential day of the year. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

-- The return value is 100. 
SELECT dayofyear('2016-04-09');

EXTRACT

Syntax

int extract(<datepart> from date|datetime|timestamp <date>)

Description
Extracts the date component specified by datepart from a date value specified by date. This function is an additional function of MaxCompute V2.0.
Parameters
- datepart: required. A value that can be set to YEAR, MONTH, DAY, HOUR, or MINUTE.
- date: required. A date value of the DATE, DATETIME, TIMESTAMP, or STRING type. The value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss.ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of datepart is not YEAR, MONTH, DAY, HOUR, or MINUTE, an error is returned.
- If the value of datepart is null, an error is returned.
- If the value of date is not of the DATE, DATETIME, TIMESTAMP, or STRING type or is null, null is returned.

Examples

Examples of static data

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select  extract(year from '2019-05-01 11:21:00') year
         ,extract(month from '2019-05-01 11:21:00') month
         ,extract(day from '2019-05-01 11:21:00') day
         ,extract(hour from '2019-05-01 11:21:00') hour
         ,extract(minute from '2019-05-01 11:21:00') minute;
-- The following result is returned: 
+------+-------+------+------+--------+
| year | month | day  | hour | minute |
+------+-------+------+------+--------+
| 2019 | 5     | 1    | 11   | 21     |
+------+-------+------+------+--------+
-- The return value is null. 
select  extract(year from null);

Examples of table data

Extract the specified date components from date values in the timestamp1 column. Data in Sample data is used in this example. Sample statements:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select timestamp1, extract(year from timestamp1) year, timestamp2, extract(month from timestamp2) month from mf_date_fun_t;

The following result is returned:

+-------------------------------+------+-------------------------------+-------+
| timestamp1                    | year | timestamp2                    | month |
+-------------------------------+------+-------------------------------+-------+
| 2021-01-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-02-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-03-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-04-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-05-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-06-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-07-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-08-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-09-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
| 2021-10-11 00:00:00.123456789 | 2021 | 2021-10-11 00:00:00.123456789 | 10    |
+-------------------------------+------+-------------------------------+-------+

FROM_UNIXTIME

Syntax

datetime from_unixtime(bigint <unixtime>)

Description
Converts unixtime of the BIGINT type into a date value of the DATETIME type.
Parameters
unixtime: required. A date value of the BIGINT type in the UNIX format. The value of this parameter is accurate to the second. Valid values: [-62167305600, 253402387200].
Note
If the input value is of the STRING, DOUBLE, or DECIMAL type and the MaxCompute V1.0 data type edition is used in your project, the input value is implicitly converted into the BIGINT type before calculation.
Return value
A value of the DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss format. If the value of unixtime is null, null is returned.
Note
You can run the set odps.sql.hive.compatible=true; command to enable the Hive-compatible data type edition. In the Hive-compatible data type edition, take note of the following rules:
- If the input value is of the STRING type, a date value of the STRING type is returned.
- You can specify the second input parameter to determine the format of the return value. For example, you can specify the date and time format for the return value in the second input parameter to yyyy-MM-dd or YYYY-MM-dd. yyyy represents the calendar year and YYYY represents the year of the week. The year of the week may be inconsistent with the actual year. Exercise caution when you use the year of the week format.

Examples

Examples of static data

-- The return value is 1973-11-30 05:33:09. 
select from_unixtime(123456789);

-- The return value is 1973-11-30 05:33:09. 
set odps.sql.type.system.odps2=false;
select from_unixtime('123456789');

-- The return value is null. 
select from_unixtime(null);

Examples of table data

Convert values in the date4 column into date values. Data in Sample data is used in this example. Sample statement:

select date4, from_unixtime(date4) as date4_from_unixtime from mf_date_fun_t;

The following result is returned:

+------------+---------------------+
| date4      | date4_from_unixtime |
+------------+---------------------+
| 123456780  | 1973-11-30 05:33:00 |
| 123456781  | 1973-11-30 05:33:01 |
| 123456782  | 1973-11-30 05:33:02 |
| 123456783  | 1973-11-30 05:33:03 |
| 123456784  | 1973-11-30 05:33:04 |
| 123456785  | 1973-11-30 05:33:05 |
| 123456786  | 1973-11-30 05:33:06 |
| 123456787  | 1973-11-30 05:33:07 |
| 123456788  | 1973-11-30 05:33:08 |
| 123456789  | 1973-11-30 05:33:09 |
+------------+---------------------+

FROM_UTC_TIMESTAMP

Syntax

timestamp from_utc_timestamp({any primitive type}*, string <timezone>)

Description
Returns a timestamp that is converted from Coordinated Universal Time (UTC) into a specified time zone. This function is an additional function of MaxCompute V2.0.
Important
The return value of the FROM_UTC_TIMESTAMP function is also affected by the value of odps.sql.timezone that you configure for your project. If you configure odps.sql.timezone=Asia/Shanghai, the UTC+8 time zone is used, which is 8 hours ahead of UTC. In this case, 8 hours are added to the calculation result of the FROM_UTC_TIMESTAMP function. For example, the FROM_UTC_TIMESTAMP(0, 'Asia/Shanghai') function converts a timestamp with UTC+0 into a timestamp with Beijing time (UTC+08:00), and the calculation result is 0 + 8 x 3600 = 28800. The odps.sql.timezone=Asia/Shanghai configuration requires another 8-hour offset. As a result, the return value is 1970-01-01 16:00:00.
Parameters
- {any primitive type}*: required. A timestamp of the TIMESTAMP, DATETIME, TINYINT, SMALLINT, INT, or BIGINT type. If the value is of the TINYINT, SMALLINT, INT, or BIGINT type, the time unit is accurate to the millisecond.
- timezone: required. The new time zone.
  Note
  You can search for the time zone list by using a search engine.
Return value
A value of the TIMESTAMP type is returned. The return value is in the yyyy-mm-dd hh:mi:ss.ff3 format. The return value varies based on the following rules:
- If the value of {any primitive type}* is not of the TIMESTAMP, DATETIME, TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
- If the value of {any primitive type}* is null, an error is returned.
- If the value of timezone is null, null is returned.

Examples

Examples of static data

-- The time unit of the input value is accurate to the millisecond and the return value is 2017-08-01 04:24:00.0. 
select from_utc_timestamp(1501557840000, 'PST'); 

-- The return value is 1970-01-30 08:00:00.0. 
select from_utc_timestamp('1970-01-30 16:00:00','PST'); 

-- The return value is 1970-01-29 16:00:00.0. 
select from_utc_timestamp('1970-01-30','PST'); 

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with the SQL statement. The return value is 2011-12-25 17:00:00:00.123. 
set odps.sql.type.system.odps2=true;
select from_utc_timestamp(timestamp '2011-12-25 09:00:00.123456', 'Asia/Shanghai');

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with the SQL statement. The return value is 2011-12-25 01:55:00.0. 
set odps.sql.type.system.odps2=true;
select from_utc_timestamp(timestamp '2011-12-25 06:55:00', 'America/Toronto');

-- The return value is null. 
select from_utc_timestamp('1970-01-30',null);

Examples of table data

Convert date values in the datetime1 and timestamp1 columns into timestamps in a specified time zone. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select datetime1, from_utc_timestamp(datetime1,'PST') pst, timestamp1, from_utc_timestamp(timestamp1,'Asia/Shanghai') asia from mf_date_fun_t;

The following result is returned:

+---------------------+---------------------+-------------------------------+-------------------------------+
| datetime1           | pst                 | timestamp1                    | asia                          |
+---------------------+---------------------+-------------------------------+-------------------------------+
| 2021-11-29 00:01:00 | 2021-11-28 16:01:00 | 2021-01-11 00:00:00.123456789 | 2021-01-11 08:00:00.123456789 |
| 2021-11-28 00:02:00 | 2021-11-27 16:02:00 | 2021-02-11 00:00:00.123456789 | 2021-02-11 08:00:00.123456789 |
| 2021-11-27 00:03:00 | 2021-11-26 16:03:00 | 2021-03-11 00:00:00.123456789 | 2021-03-11 08:00:00.123456789 |
| 2021-11-26 00:04:00 | 2021-11-25 16:04:00 | 2021-04-11 00:00:00.123456789 | 2021-04-11 08:00:00.123456789 |
| 2021-11-25 00:05:00 | 2021-11-24 16:05:00 | 2021-05-11 00:00:00.123456789 | 2021-05-11 08:00:00.123456789 |
| 2021-11-24 00:06:00 | 2021-11-23 16:06:00 | 2021-06-11 00:00:00.123456789 | 2021-06-11 08:00:00.123456789 |
| 2021-11-23 00:07:00 | 2021-11-22 16:07:00 | 2021-07-11 00:00:00.123456789 | 2021-07-11 08:00:00.123456789 |
| 2021-11-22 00:08:00 | 2021-11-21 16:08:00 | 2021-08-11 00:00:00.123456789 | 2021-08-11 08:00:00.123456789 |
| 2021-11-21 00:09:00 | 2021-11-20 16:09:00 | 2021-09-11 00:00:00.123456789 | 2021-09-11 08:00:00.123456789 |
| 2021-11-20 00:10:00 | 2021-11-19 16:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-11 08:00:00.123456789 |
+---------------------+---------------------+-------------------------------+-------------------------------+

GETDATE

Syntax
```
datetime getdate()
```
Description
Returns the current system time as a date value. MaxCompute uses UTC+8 as the standard time zone.
Return value
The current date and time are returned, which are of the DATETIME type.
Note
In MaxCompute SQL, GETDATE always returns a fixed value. The return value is an arbitrary time during the execution of the MaxCompute SQL task. The time is accurate to the second. If you enable the MaxCompute V2.0 data type edition, the time is accurate to the millisecond.

HOUR

Syntax

int hour(datetime|timestamp|string <date>)

Description
Returns the hour component of a date value.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd hh:mi:ss or yyyy-mm-dd hh:mi:ss.ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings. This function is an additional function of MaxCompute V2.0.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 12. 
select hour('2014-09-01 12:00:00');

-- The return value is 12. 
select hour('12:00:00');

-- The return value is null. 
select hour('20140901120000');

-- The return value is null. 
select hour(null);

Examples of table data

Obtain the hour components of date values in the datetime1 and timestamp1 columns. Data in Sample data is used in this example. Sample statements:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select datetime1, hour(datetime1) as datetime1_hour, timestamp1, hour(timestamp1) as timestamp1_hour from mf_date_fun_t;

The following result is returned:

+---------------------+----------------+-------------------------------+-----------------+
| datetime1           | datetime1_hour | timestamp1                    | timestamp1_hour |
+---------------------+----------------+-------------------------------+-----------------+
| 2021-11-29 00:01:00 | 0              | 2021-01-11 00:00:00.123456789 | 0               |
| 2021-11-28 00:02:00 | 0              | 2021-02-11 00:00:00.123456789 | 0               |
| 2021-11-27 00:03:00 | 0              | 2021-03-11 00:00:00.123456789 | 0               |
| 2021-11-26 00:04:00 | 0              | 2021-04-11 00:00:00.123456789 | 0               |
| 2021-11-25 00:05:00 | 0              | 2021-05-11 00:00:00.123456789 | 0               |
| 2021-11-24 00:06:00 | 0              | 2021-06-11 00:00:00.123456789 | 0               |
| 2021-11-23 00:07:00 | 0              | 2021-07-11 00:00:00.123456789 | 0               |
| 2021-11-22 00:08:00 | 0              | 2021-08-11 00:00:00.123456789 | 0               |
| 2021-11-21 00:09:00 | 0              | 2021-09-11 00:00:00.123456789 | 0               |
| 2021-11-20 00:10:00 | 0              | 2021-10-11 00:00:00.123456789 | 0               |
+---------------------+----------------+-------------------------------+-----------------+

ISDATE

Syntax

boolean isdate(string <date>, string <format>)

Description
Determines whether a date string can be converted to a date value in a specified format. If the date string can be converted into a date value in the specified format, true is returned. Otherwise, false is returned.
Parameters
- date: required. Values of the STRING type. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted to a value of the STRING type before calculation.
- format: required. A constant of the STRING type. This parameter does not support Extended Date/Time Format (EDTF). If redundant format strings exist in format, this function converts the date string that corresponds to the first format string into a date value. The rest strings are considered delimiters. For example, isdate("1234-yyyy", "yyyy-yyyy") returns true.
Return value
A value of the BOOLEAN type is returned. If the value of date or format is null, null is returned.

Examples

Examples of static data

-- The return value is true. 
select isdate('2021-10-11','yyyy-mm-dd');

-- The return value is false. 
set odps.sql.type.system.odps2=false;
select isdate(1678952314,'yyyy-mm-dd');

Examples of table data

Determine whether date strings in the date3 column can be converted into date values in a specified format. Data in Sample data is used in this example. Sample statement:

select date3, isdate(date3,'yyyy-mm-dd') as date3_isdate from mf_date_fun_t;

The following result is returned:

+------------+--------------+
| date3      | date3_isdate |
+------------+--------------+
| 2021-11-20 | true         |
| 2021-11-21 | true         |
| 2021-11-22 | true         |
| 2021-11-23 | true         |
| 2021-11-24 | true         |
| 2021-11-25 | true         |
| 2021-11-26 | true         |
| 2021-11-27 | true         |
| 2021-11-28 | true         |
| 2021-11-29 | true         |
+------------+--------------+

LAST_DAY

Syntax

string last_day(date|datetime|timestamp|string <date>)

Description
Returns the last day of the month in which a date value falls. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATE, DATETIME, TIMESTAMP, or STRING type. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and must not contain extra strings.
Return value
A value of the STRING type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
- If the value of date is not of the DATE, DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, an error is returned.

Examples

Examples of static data

-- The return value is 2017-03-31. 
select last_day('2017-03-04');

-- The return value is 2017-07-31. 
select last_day('2017-07-04 11:40:00');

-- The return value is null. 
select last_day('20170304');

Examples of table data

Obtain the last day of the month in which each date value in the date1, datetime1, timestamp1, and date3 columns falls. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, last_day(date1) as date1_lastday, datetime1, last_day(datetime1) as datetime1_lastday, timestamp1, last_day(timestamp1) as timestamp1_lastday, date3, last_day(date3) as date3_lastday from mf_date_fun_t;

The following result is returned:

+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
| date1      | date1_lastday | datetime1           | datetime1_lastday | timestamp1                    | timestamp1_lastday | date3      | date3_lastday |
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
| 2021-11-29 | 2021-11-30    | 2021-11-29 00:01:00 | 2021-11-30        | 2021-01-11 00:00:00.123456789 | 2021-01-31         | 2021-11-20 | 2021-11-30    |
| 2021-11-28 | 2021-11-30    | 2021-11-28 00:02:00 | 2021-11-30        | 2021-02-11 00:00:00.123456789 | 2021-02-28         | 2021-11-21 | 2021-11-30    |
| 2021-11-27 | 2021-11-30    | 2021-11-27 00:03:00 | 2021-11-30        | 2021-03-11 00:00:00.123456789 | 2021-03-31         | 2021-11-22 | 2021-11-30    |
| 2021-11-26 | 2021-11-30    | 2021-11-26 00:04:00 | 2021-11-30        | 2021-04-11 00:00:00.123456789 | 2021-04-30         | 2021-11-23 | 2021-11-30    |
| 2021-11-25 | 2021-11-30    | 2021-11-25 00:05:00 | 2021-11-30        | 2021-05-11 00:00:00.123456789 | 2021-05-31         | 2021-11-24 | 2021-11-30    |
| 2021-11-24 | 2021-11-30    | 2021-11-24 00:06:00 | 2021-11-30        | 2021-06-11 00:00:00.123456789 | 2021-06-30         | 2021-11-25 | 2021-11-30    |
| 2021-11-23 | 2021-11-30    | 2021-11-23 00:07:00 | 2021-11-30        | 2021-07-11 00:00:00.123456789 | 2021-07-31         | 2021-11-26 | 2021-11-30    |
| 2021-11-22 | 2021-11-30    | 2021-11-22 00:08:00 | 2021-11-30        | 2021-08-11 00:00:00.123456789 | 2021-08-31         | 2021-11-27 | 2021-11-30    |
| 2021-11-21 | 2021-11-30    | 2021-11-21 00:09:00 | 2021-11-30        | 2021-09-11 00:00:00.123456789 | 2021-09-30         | 2021-11-28 | 2021-11-30    |
| 2021-11-20 | 2021-11-30    | 2021-11-20 00:10:00 | 2021-11-30        | 2021-10-11 00:00:00.123456789 | 2021-10-31         | 2021-11-29 | 2021-11-30    |
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+

LASTDAY

Syntax
```
datetime lastday(datetime <date>)
```
Description
Returns the last day of the month in which a date value falls. Only the day component is truncated. The hour, minute, and second components are expressed as 00:00:00.
Parameters
date: a date value of the DATETIME type. The date value is 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 DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss format. 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, an error is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 2013-06-30 00:00:00. 
select lastday (datetime '2013-06-08 01:10:00');

-- The return value is 2013-06-30 00:00:00. 
set odps.sql.type.system.odps2=false;
select lastday ('2013-06-08 01:10:00');

-- The return value is null. 
select lastday (null);

Examples of table data

Obtain the last day of the month in which each date value in the datetime1 column falls. Data in Sample data is used in this example. Sample statement:

select datetime1, lastday(datetime1) as datetime1_lastday from mf_date_fun_t;

The following result is returned:

+---------------------+---------------------+
| datetime1           | datetime1_lastday   |
+---------------------+---------------------+
| 2021-11-29 00:01:00 | 2021-11-30 00:00:00 |
| 2021-11-28 00:02:00 | 2021-11-30 00:00:00 |
| 2021-11-27 00:03:00 | 2021-11-30 00:00:00 |
| 2021-11-26 00:04:00 | 2021-11-30 00:00:00 |
| 2021-11-25 00:05:00 | 2021-11-30 00:00:00 |
| 2021-11-24 00:06:00 | 2021-11-30 00:00:00 |
| 2021-11-23 00:07:00 | 2021-11-30 00:00:00 |
| 2021-11-22 00:08:00 | 2021-11-30 00:00:00 |
| 2021-11-21 00:09:00 | 2021-11-30 00:00:00 |
| 2021-11-20 00:10:00 | 2021-11-30 00:00:00 |
+---------------------+---------------------+

MINUTE

Syntax

int minute(datetime|timestamp|string <date>)

Description
Returns the minute component of a date value. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd hh:mi:ss or yyyy-mm-dd hh:mi:ss.ff3 format.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 30. 
select minute('2014-09-01 12:30:00'); 

-- The return value is 30. 
select minute('12:30:00');

-- The return value is null. 
select minute('20140901120000');

-- The return value is null. 
select minute(null);

Examples of table data

Obtain the minute components of date values in the datetime1 and timestamp1 columns. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select datetime1, minute(datetime1) as datetime1_minute, timestamp1, minute(timestamp1) as timestamp1_minute from mf_date_fun_t;

The following result is returned:

+---------------------+------------------+-------------------------------+-------------------+
| datetime1           | datetime1_minute | timestamp1                    | timestamp1_minute |
+---------------------+------------------+-------------------------------+-------------------+
| 2021-11-29 00:01:00 | 1                | 2021-01-11 00:00:00.123456789 | 0                 |
| 2021-11-28 00:02:00 | 2                | 2021-02-11 00:00:00.123456789 | 0                 |
| 2021-11-27 00:03:00 | 3                | 2021-03-11 00:00:00.123456789 | 0                 |
| 2021-11-26 00:04:00 | 4                | 2021-04-11 00:00:00.123456789 | 0                 |
| 2021-11-25 00:05:00 | 5                | 2021-05-11 00:00:00.123456789 | 0                 |
| 2021-11-24 00:06:00 | 6                | 2021-06-11 00:00:00.123456789 | 0                 |
| 2021-11-23 00:07:00 | 7                | 2021-07-11 00:00:00.123456789 | 0                 |
| 2021-11-22 00:08:00 | 8                | 2021-08-11 00:00:00.123456789 | 0                 |
| 2021-11-21 00:09:00 | 9                | 2021-09-11 00:00:00.123456789 | 0                 |
| 2021-11-20 00:10:00 | 10               | 2021-10-11 00:00:00.123456789 | 0                 |
+---------------------+------------------+-------------------------------+-------------------+

MONTH

Syntax

int month(datetime|timestamp|date|string <date>)

Description
Returns the month in which a date value falls. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 9. 
select month('2014-09-01');

-- The return value is null. 
select month('20140901');

-- The return value is null. 
select month(null);

Examples of table data

Obtain the month component of each date value in the date1, datetime1, timestamp1, and date3 columns. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, month(date1) as date1_month, datetime1, month(datetime1) as datetime1_month, timestamp1, month(timestamp1) as timestamp1_month, date3, month(date3) as date3_month from mf_date_fun_t;

The following result is returned:

+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
| date1      | date1_month | datetime1           | datetime1_month | timestamp1                    | timestamp1_month | date3      | date3_month |
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+
| 2021-11-29 | 11          | 2021-11-29 00:01:00 | 11              | 2021-01-11 00:00:00.123456789 | 1                | 2021-11-20 | 11          |
| 2021-11-28 | 11          | 2021-11-28 00:02:00 | 11              | 2021-02-11 00:00:00.123456789 | 2                | 2021-11-21 | 11          |
| 2021-11-27 | 11          | 2021-11-27 00:03:00 | 11              | 2021-03-11 00:00:00.123456789 | 3                | 2021-11-22 | 11          |
| 2021-11-26 | 11          | 2021-11-26 00:04:00 | 11              | 2021-04-11 00:00:00.123456789 | 4                | 2021-11-23 | 11          |
| 2021-11-25 | 11          | 2021-11-25 00:05:00 | 11              | 2021-05-11 00:00:00.123456789 | 5                | 2021-11-24 | 11          |
| 2021-11-24 | 11          | 2021-11-24 00:06:00 | 11              | 2021-06-11 00:00:00.123456789 | 6                | 2021-11-25 | 11          |
| 2021-11-23 | 11          | 2021-11-23 00:07:00 | 11              | 2021-07-11 00:00:00.123456789 | 7                | 2021-11-26 | 11          |
| 2021-11-22 | 11          | 2021-11-22 00:08:00 | 11              | 2021-08-11 00:00:00.123456789 | 8                | 2021-11-27 | 11          |
| 2021-11-21 | 11          | 2021-11-21 00:09:00 | 11              | 2021-09-11 00:00:00.123456789 | 9                | 2021-11-28 | 11          |
| 2021-11-20 | 11          | 2021-11-20 00:10:00 | 11              | 2021-10-11 00:00:00.123456789 | 10               | 2021-11-29 | 11          |
+------------+-------------+---------------------+-----------------+-------------------------------+------------------+------------+-------------+

MONTHS_BETWEEN

Syntax

double months_between(datetime|timestamp|date|string <date1>, datetime|timestamp|date|string <date2>)

Description
Returns the number of months between date1 and date2. This function is an additional function of MaxCompute V2.0.
Parameters
date1 and date2: required. Values of the DATETIME, TIMESTAMP, DATE, or STRING type. The input values are in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, yyyy-mm-dd hh:mi:ss.ff3 format. If the input values are of the STRING type, the values must include at least the yyyy-mm-dd part and must not contain extra strings.
Return value
A value of the DOUBLE type is returned. The return value varies based on the following rules:
- If date1 is later than date2, a positive value is returned. If date2 is later than date1, a negative value is returned.
- If date1 and date2 correspond to the last days of two months, the return value is an integer that represents the number of months. Otherwise, the return value is calculated by using the following formula: (date1 - date2)/31.
- If the value of date1 or date2 is null, null is returned.

Examples

Examples of static data

-- The return value is 3.9495967741935485. 
select months_between('1997-02-28 10:30:00', '1996-10-30');

-- The return value is -3.9495967741935485. 
select months_between('1996-10-30','1997-02-28 10:30:00' );

-- The return value is -3.0. 
select months_between('1996-09-30','1996-12-31');

-- The return value is null. 
select months_between('1996-09-30',null);

Examples of table data

Calculate the number of months between date values in the timestamp1 and timestamp2 columns. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select timestamp1, timestamp2, months_between(timestamp1, timestamp2) from mf_date_fun_t;

The following result is returned:

+-------------------------------+-------------------------------+------------+
| timestamp1                    | timestamp2                    | _c2        |
+-------------------------------+-------------------------------+------------+
| 2021-01-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -9.0       |
| 2021-02-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -8.0       |
| 2021-03-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -7.0       |
| 2021-04-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -6.0       |
| 2021-05-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -5.0       |
| 2021-06-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -4.0       |
| 2021-07-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -3.0       |
| 2021-08-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -2.0       |
| 2021-09-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | -1.0       |
| 2021-10-11 00:00:00.123456789 | 2021-10-11 00:00:00.123456789 | 0.0        |
+------------+------------+------------+

NEXT_DAY

Syntax

string next_day(timestamp|date|datetime|string <startdate>, string <week>)

Description
Returns the date of the first day that is later than startdate and matches the week value. The date of the specified day in the next week is returned. This function is an additional function of MaxCompute V2.0.
Parameters
- startdate: required. A value of the TIMESTAMP, DATE, DATETIME, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss.ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
- week: required. A value of the STRING type. The value of this parameter can be the first two or three letters of a weekday or the full name of a weekday, such as MO, TUE, or FRIDAY.
Return value
A value of the STRING type is returned. The return value is in the yyyy-mm-dd format. The return value varies based on the following rules:
- If the value of date is not of the TIMESTAMP, DATE, DATETIME, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, an error is returned.
- If the value of week is null, null is returned.

Examples

Examples of static data

-- The return value is 2017-08-08. 
select next_day('2017-08-01','TU');

-- The return value is 2017-08-08. 
select next_day('2017-08-01 23:34:00','TU');

-- The return value is null. 
select next_day('20170801','TU');

-- The return value is null. 
select next_day('2017-08-01 23:34:00',null);

Examples of table data

Obtain the date of the day in the next week to which each date value in the date1, datetime1, timestamp1, and date3 columns corresponds. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, next_day(date1,'MON') as date1_next_day, datetime1, next_day(datetime1,'TUE') as datetime1_next_day, timestamp1, next_day(timestamp1,'WED') as timestamp1_next_day, date3, next_day(date3,'THU') as date3_next_day from mf_date_fun_t;

The following result is returned:

+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+
| date1      | date1_next_day | datetime1           | datetime1_next_day | timestamp1                    | timestamp1_next_day | date3      | date3_next_day |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+
| 2021-11-29 | 2021-12-06     | 2021-11-29 00:01:00 | 2021-11-30         | 2021-01-11 00:00:00.123456789 | 2021-01-13          | 2021-11-20 | 2021-11-25     |
| 2021-11-28 | 2021-11-29     | 2021-11-28 00:02:00 | 2021-11-30         | 2021-02-11 00:00:00.123456789 | 2021-02-17          | 2021-11-21 | 2021-11-25     |
| 2021-11-27 | 2021-11-29     | 2021-11-27 00:03:00 | 2021-11-30         | 2021-03-11 00:00:00.123456789 | 2021-03-17          | 2021-11-22 | 2021-11-25     |
| 2021-11-26 | 2021-11-29     | 2021-11-26 00:04:00 | 2021-11-30         | 2021-04-11 00:00:00.123456789 | 2021-04-14          | 2021-11-23 | 2021-11-25     |
| 2021-11-25 | 2021-11-29     | 2021-11-25 00:05:00 | 2021-11-30         | 2021-05-11 00:00:00.123456789 | 2021-05-12          | 2021-11-24 | 2021-11-25     |
| 2021-11-24 | 2021-11-29     | 2021-11-24 00:06:00 | 2021-11-30         | 2021-06-11 00:00:00.123456789 | 2021-06-16          | 2021-11-25 | 2021-12-02     |
| 2021-11-23 | 2021-11-29     | 2021-11-23 00:07:00 | 2021-11-30         | 2021-07-11 00:00:00.123456789 | 2021-07-14          | 2021-11-26 | 2021-12-02     |
| 2021-11-22 | 2021-11-29     | 2021-11-22 00:08:00 | 2021-11-23         | 2021-08-11 00:00:00.123456789 | 2021-08-18          | 2021-11-27 | 2021-12-02     |
| 2021-11-21 | 2021-11-22     | 2021-11-21 00:09:00 | 2021-11-23         | 2021-09-11 00:00:00.123456789 | 2021-09-15          | 2021-11-28 | 2021-12-02     |
| 2021-11-20 | 2021-11-22     | 2021-11-20 00:10:00 | 2021-11-23         | 2021-10-11 00:00:00.123456789 | 2021-10-13          | 2021-11-29 | 2021-12-02     |
+------------+----------------+---------------------+--------------------+-------------------------------+---------------------+------------+----------------+

NOW

Syntax
```
datetime NOW()
```
Description
Returns the current system date and time.
Return value
A value of the DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss.SSS format.
Examples
- No format is specified. The millisecond part in the returned value may include 1, 2, or 3 bits.
```
select now();
```
  The following result is returned:
```
+------+
| _c0  |
+------+
| 2023-06-13 10:53:24.967 |
+------+
```
- The time format is specified.
```
select  date_format(now(),'yyyy-MM-dd hh:mm:ss.SSS') ;
```
  The following result is returned:
```
+-----+
| _c0 |
+-----+
| 2023-06-13 10:53:53.899 |
+-----+
```

QUARTER

Syntax

int quarter (datetime|timestamp|date|string <date>)

Description
Returns the quarter in which a date value falls. Valid values: 1 to 4. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 4. 
select quarter('1970-11-12 10:00:00');

-- The return value is 4. 
select quarter('1970-11-12');

-- The return value is null. 
select quarter(null);

Examples of table data

Obtain the quarter in which each date value in the date1, datetime1, timestamp1, and date3 columns falls. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, quarter(date1) as date1_quarter, datetime1, quarter(datetime1) as datetime1_quarter, timestamp1, quarter(timestamp1) as timestamp1_quarter, date3, quarter(date3) as date3_quarter from mf_date_fun_t;

The following result is returned:

+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
| date1      | date1_quarter | datetime1           | datetime1_quarter | timestamp1                    | timestamp1_quarter | date3      | date3_quarter |
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+
| 2021-11-29 | 4             | 2021-11-29 00:01:00 | 4                 | 2021-01-11 00:00:00.123456789 | 1                  | 2021-11-20 | 4             |
| 2021-11-28 | 4             | 2021-11-28 00:02:00 | 4                 | 2021-02-11 00:00:00.123456789 | 1                  | 2021-11-21 | 4             |
| 2021-11-27 | 4             | 2021-11-27 00:03:00 | 4                 | 2021-03-11 00:00:00.123456789 | 1                  | 2021-11-22 | 4             |
| 2021-11-26 | 4             | 2021-11-26 00:04:00 | 4                 | 2021-04-11 00:00:00.123456789 | 2                  | 2021-11-23 | 4             |
| 2021-11-25 | 4             | 2021-11-25 00:05:00 | 4                 | 2021-05-11 00:00:00.123456789 | 2                  | 2021-11-24 | 4             |
| 2021-11-24 | 4             | 2021-11-24 00:06:00 | 4                 | 2021-06-11 00:00:00.123456789 | 2                  | 2021-11-25 | 4             |
| 2021-11-23 | 4             | 2021-11-23 00:07:00 | 4                 | 2021-07-11 00:00:00.123456789 | 3                  | 2021-11-26 | 4             |
| 2021-11-22 | 4             | 2021-11-22 00:08:00 | 4                 | 2021-08-11 00:00:00.123456789 | 3                  | 2021-11-27 | 4             |
| 2021-11-21 | 4             | 2021-11-21 00:09:00 | 4                 | 2021-09-11 00:00:00.123456789 | 3                  | 2021-11-28 | 4             |
| 2021-11-20 | 4             | 2021-11-20 00:10:00 | 4                 | 2021-10-11 00:00:00.123456789 | 4                  | 2021-11-29 | 4             |
+------------+---------------+---------------------+-------------------+-------------------------------+--------------------+------------+---------------+

SECOND

Syntax

int second(datetime|timestamp|string <date>)

Description
Returns the second component of a date value. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, or STRING type. The date value is in the yyyy-mm-dd hh:mi:ss or yyyy-mm-dd hh:mi:ss.ff3 format.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 45. 
select second('2014-09-01 12:30:45');

-- The return value is 45. 
select second('12:30:45');

-- The return value is null. 
select second('20140901123045');

-- The return value is null. 
select second(null);

Examples of table data

Obtain the second components of date values in the datetime1 and timestamp1 columns. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
elect datetime1, second(datetime1) as datetime1_second, timestamp1, second(timestamp1) as timestamp1_second from mf_date_fun_t;

The following result is returned:

+---------------------+------------------+-------------------------------+-------------------+
| datetime1           | datetime1_second | timestamp1                    | timestamp1_second |
+---------------------+------------------+-------------------------------+-------------------+
| 2021-11-29 00:01:00 | 0                | 2021-01-11 00:00:00.123456789 | 0                 |
| 2021-11-28 00:02:00 | 0                | 2021-02-11 00:00:00.123456789 | 0                 |
| 2021-11-27 00:03:00 | 0                | 2021-03-11 00:00:00.123456789 | 0                 |
| 2021-11-26 00:04:00 | 0                | 2021-04-11 00:00:00.123456789 | 0                 |
| 2021-11-25 00:05:00 | 0                | 2021-05-11 00:00:00.123456789 | 0                 |
| 2021-11-24 00:06:00 | 0                | 2021-06-11 00:00:00.123456789 | 0                 |
| 2021-11-23 00:07:00 | 0                | 2021-07-11 00:00:00.123456789 | 0                 |
| 2021-11-22 00:08:00 | 0                | 2021-08-11 00:00:00.123456789 | 0                 |
| 2021-11-21 00:09:00 | 0                | 2021-09-11 00:00:00.123456789 | 0                 |
| 2021-11-20 00:10:00 | 0                | 2021-10-11 00:00:00.123456789 | 0                 |
+---------------------+------------------+-------------------------------+-------------------+

TO_CHAR

Syntax

string to_char(datetime <date>, string <format>)

Description
Converts a date value of the DATETIME type into a string in a specified format.
Parameters
- date: required. A value of the DATETIME type. The date value is 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.
- format: required. A constant of the STRING type. This parameter specifies the date format. In the format parameter, the date format part is replaced by the related data and the other characters remain unchanged in the output.
Return value
A value of the STRING 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, an error is returned.
- If the value of date is null, an error is returned.
- If the value of format is null, null is returned.

Examples

Examples of static data

Example 1

--If the MaxCompute client runs in Windows, Chinese characters may not be properly displayed in the returned result. 
SELECT TO_CHAR(DATETIME '2010-12-03 00:00:00','Alibaba Cloud Financial Services yyyy-mm*dd');

The following result is returned:

+------------+
| _c0        |
+------------+
| Alibaba Cloud Financial Services 2010-12*03 |
+------------+

Example 2

SELECT TO_CHAR(DATETIME '2008-07-18 00:00:00','yyyymmdd');

The following result is returned:

+------------+
| _c0        |
+------------+
| 20080718   |
+------------+

Example 3

SET odps.sql.type.system.odps2=false;
SELECT TO_CHAR('2008-07-18 00:00:00', 'yyyymmdd');

The following result is returned:

+------------+
| _c0        |
+------------+
| 20080718   |
+------------+

Example 4

--'Alibaba 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be written as 'Alibaba 2010-12*03'。
SELECT TO_CHAR(datetime'Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd');

Example 5

--'20102401' is not a standard DATETIME value, and an error is returned. The value must be written as '2010-01-24 00:00:00'。
SELECT TO_CHAR(datetime'20102401', 'yyyy');

Example 6

SELECT TO_CHAR(datetime'2010-12-03 00:00:00', null);

The following result is returned:

+------------+
| _c0        |
+------------+
| NULL       |
+------------+

Examples of table data

Convert date values in the datetime1 column into strings in a specified format. Data in Sample data is used in this example. Sample statement:

SELECT  datetime1
        ,TO_CHAR(datetime1,'yyyy-mm-dd') AS datetime1_to_char
FROM    mf_date_fun_t;

The following result is returned:

+---------------------+-------------------+
| datetime1           | datetime1_to_char |
+---------------------+-------------------+
| 2021-11-29 00:01:00 | 2021-11-29        |
| 2021-11-28 00:02:00 | 2021-11-28        |
| 2021-11-27 00:03:00 | 2021-11-27        |
| 2021-11-26 00:04:00 | 2021-11-26        |
| 2021-11-25 00:05:00 | 2021-11-25        |
| 2021-11-24 00:06:00 | 2021-11-24        |
| 2021-11-23 00:07:00 | 2021-11-23        |
| 2021-11-22 00:08:00 | 2021-11-22        |
| 2021-11-21 00:09:00 | 2021-11-21        |
| 2021-11-20 00:10:00 | 2021-11-20        |
+---------------------+-------------------+

TO_DATE

Syntax

datetime|date to_date(string <date>[, string <format>])

Description
Converts a string into a date value in a specified format.
Parameters
- date: required. A date value of the STRING type. This parameter specifies the date string that you want to convert. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME data type, the value is implicitly converted into a value of the STRING type before calculation. The date string can also be in the ISO 8601 format.
- format: optional. A constant of the STRING type. This parameter specifies the date format. format does not support extended date and time format (EDTF). Other characters are omitted as invalid characters during parsing.
  The value of format must contain yyyy. Otherwise, null is returned. If redundant format strings exist in format, this function converts the date string that corresponds to the first format string into a date value. The rest strings are considered delimiters. For example, to_date("1234-2234", "yyyy-yyyy") returns 1234-01-01 00:00:00.
  The format consists of different date components. yyyy indicates a 4-digit year. mm indicates a 2-digit month. dd indicates a 2-digit day. hh indicates an hour based on the 24-hour clock. mi indicates a 2-digit minute. ss indicates a 2-digit second. ff3 indicates a 3-digit millisecond.
Return value
A value of the DATE or DATETIME type is returned.
- If the format parameter is not included in the input parameters and the string that needs to be converted is in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format, a value of the DATE type that is in the yyyy-mm-dd format is returned. If the format parameter is not included in the input parameters and the string that needs to be converted is not in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format, null is returned.
- If the format parameter is included in the input parameters, a value of the DATETIME type that is in the yyyy-mm-dd hh:mi:ss format is returned. If the value of date or format is null, null is returned.

Examples

Examples of static data

-- The return value is 2010-12-03 00:00:00. 
select to_date('Alibaba 2010-12*03', 'Alibaba yyyy-mm*dd')

-- The return value is 2008-07-18 00:00:00. 
select to_date('20080718', 'yyyymmdd');

-- The return value is 2008-07-18 20:30:00. 
select to_date('200807182030','yyyymmddhhmi');

-- '2008718' cannot be converted to a standard date value, and an error is returned. The value must be written as '20080718'. 
select to_date('2008718', 'yyyymmdd');

-- 'Alibaba 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be written as 'Alibaba 2010-12*03'. 
select to_date('Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd');

-- '2010-24-01' cannot be converted into a standard date value, and an error is returned. The value must be written as '2010-01-24'. 
select to_date('2010-24-01', 'yyyy-mm-dd');

-- The return value is 2018-10-30 15:13:12. 
select to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');

-- The return value is null. 
select to_date(null,'yyyymmdd hh-mi-ss.ff3');

-- The return value is null. 
select to_date('20181030 15-13-12.345',null);

-- If the input value is in the ISO 8601 format, the return value is 2021-09-24 13:39:34. 
select to_date('2021-09-24T13:39:34.119Z', 'yyyy-MM-ddThh:mi:ss.ff3Z');

-- The return value is 2021-09-24, and the data type is DATE. 
select to_date('2021-09-24');

-- The return value is 2021-09-24, and the data type is DATE. 
select to_date('2021-09-24 13:39:34');

-- The return value is null. 
select to_date('20210924');

Examples of table data

Convert date values in the date3 column into date values in a specified format. Data in Sample data is used in this example. Sample statement:

select date3, to_date(date3, 'yyyy-mm-dd') as date3_to_date from mf_date_fun_t;

The following result is returned:

+------------+---------------------+
| date3      | date3_to_date       |
+------------+---------------------+
| 2021-11-20 | 2021-11-20 00:00:00 |
| 2021-11-21 | 2021-11-21 00:00:00 |
| 2021-11-22 | 2021-11-22 00:00:00 |
| 2021-11-23 | 2021-11-23 00:00:00 |
| 2021-11-24 | 2021-11-24 00:00:00 |
| 2021-11-25 | 2021-11-25 00:00:00 |
| 2021-11-26 | 2021-11-26 00:00:00 |
| 2021-11-27 | 2021-11-27 00:00:00 |
| 2021-11-28 | 2021-11-28 00:00:00 |
| 2021-11-29 | 2021-11-29 00:00:00 |
+------------+---------------------+

TO_MILLIS

Syntax

bigint to_millis(datetime|timestamp <date>);

Description
Converts a date value into a UNIX timestamp that is accurate to the millisecond. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME or TIMESTAMP type. The date value is in the yyyy-mm-dd hh:mi:ss or yyyy-mm-dd hh:mi:ss.ff3 format.
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 TIMESTAMP type, an error is returned.
- If the value of date is null, an error is returned.

Examples

Examples of static data

-- The return value is 1617174900000. 
select to_millis(datetime '2021-03-31 15:15:00');

-- The return value is 1617174900000. 
set odps.sql.type.system.odps2=true;
select to_millis(timestamp '2021-03-31 15:15:00');

Examples of table data

Convert date values in the datetime1 and timestamp1 columns into UNIX timestamps that are accurate to the millisecond. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select datetime1, to_millis(datetime1) as datetime1_to_millis, timestamp1, to_millis(timestamp1) as timestamp1_to_millis from mf_date_fun_t;

The following result is returned:

+---------------------+---------------------+-------------------------------+----------------------+
| datetime1           | datetime1_to_millis | timestamp1                    | timestamp1_to_millis |
+---------------------+---------------------+-------------------------------+----------------------+
| 2021-11-29 00:01:00 | 1638115260000       | 2021-01-11 00:00:00.123456789 | 1610294400123        |
| 2021-11-28 00:02:00 | 1638028920000       | 2021-02-11 00:00:00.123456789 | 1612972800123        |
| 2021-11-27 00:03:00 | 1637942580000       | 2021-03-11 00:00:00.123456789 | 1615392000123        |
| 2021-11-26 00:04:00 | 1637856240000       | 2021-04-11 00:00:00.123456789 | 1618070400123        |
| 2021-11-25 00:05:00 | 1637769900000       | 2021-05-11 00:00:00.123456789 | 1620662400123        |
| 2021-11-24 00:06:00 | 1637683560000       | 2021-06-11 00:00:00.123456789 | 1623340800123        |
| 2021-11-23 00:07:00 | 1637597220000       | 2021-07-11 00:00:00.123456789 | 1625932800123        |
| 2021-11-22 00:08:00 | 1637510880000       | 2021-08-11 00:00:00.123456789 | 1628611200123        |
| 2021-11-21 00:09:00 | 1637424540000       | 2021-09-11 00:00:00.123456789 | 1631289600123        |
| 2021-11-20 00:10:00 | 1637338200000       | 2021-10-11 00:00:00.123456789 | 1633881600123        |
+---------------------+---------------------+-------------------------------+----------------------+

UNIX_TIMESTAMP

Syntax

bigint unix_timestamp(datetime|date|timestamp|string <date>)

Description
Converts a date value into a UNIX timestamp that is an integer.
Parameters
date: required. A date value of the DATETIME, DATE, TIMESTAMP, or STRING type. The input value is in the yyyy-mm-dd hh:mi:ss, yyyy-mm-dd, or yyyy-mm-dd hh:mi:ss.ff3 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 a value of the DATETIME type before calculation. For more information about the MaxCompute V1.0 data type edition, see MaxCompute V1.0 data type edition. If you enable the MaxCompute V2.0 data type edition, the implicit conversion fails. In this case, you must use the CAST function, such as unix_timestamp(cast(... as datetime)), to convert data types. You can also disable the MaxCompute V2.0 data type edition. For more information about the MaxCompute V2.0 data type edition, see MaxCompute V2.0 data type edition.
Note
If the input value is a constant and is not a value in the table, the input value is in the DATETIME'yyyy-mm-dd hh:mi:ss', DATE'yyyy-mm-dd', or TIMESTAMP'yyyy-mm-dd hh:mi:ss.ff3' format. If the input value does not contain the keyword of the data type and is in the 'yyyy-mm-dd hh:mi:ss' format, the input value is recognized as a value of the STRING type.
Return value
A UNIX timestamp 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, DATE, TIMESTAMP, or STRING type or the format does not meet the requirements, an error is returned or null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 1699585860. 
SELECT unix_timestamp(DATETIME'2023-11-10 11:11:00'); 

-- The return value is 1699545600.
set odps.sql.type.system.odps2=true;
SELECT unix_timestamp(DATE'2023-11-10');

-- The return value is 1699585860.
set odps.sql.type.system.odps2=true;
SELECT unix_timestamp(TIMESTAMP'2023-11-10 11:11:00.123456789');

-- The return value is 1237518660. 
set odps.sql.type.system.odps2=false;
select unix_timestamp('2009-03-20 11:11:00'); 

-- The return value is null. 
select unix_timestamp(null);

Examples of table data

Convert date values in the date1, datetime1, and timestamp1 columns into UNIX timestamps that are integers. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, unix_timestamp(date1) as date1_unix_timestamp, datetime1, unix_timestamp(datetime1) as datetime1_unix_timestamp, timestamp1, unix_timestamp(timestamp1) as timestamp1_unix_timestamp from mf_date_fun_t;

The following result is returned:

+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+
| date1      | date1_unix_timestamp | datetime1           | datetime1_unix_timestamp | timestamp1                   | timestamp1_unix_timestamp |
+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+
| 2021-11-29 | 1638115200           | 2021-11-29 00:01:00 | 1638115260               | 2021-01-11 00:00:00.123456789 | 1610294400                |
| 2021-11-28 | 1638028800           | 2021-11-28 00:02:00 | 1638028920               | 2021-02-11 00:00:00.123456789 | 1612972800                |
| 2021-11-27 | 1637942400           | 2021-11-27 00:03:00 | 1637942580               | 2021-03-11 00:00:00.123456789 | 1615392000                |
| 2021-11-26 | 1637856000           | 2021-11-26 00:04:00 | 1637856240               | 2021-04-11 00:00:00.123456789 | 1618070400                |
| 2021-11-25 | 1637769600           | 2021-11-25 00:05:00 | 1637769900               | 2021-05-11 00:00:00.123456789 | 1620662400                |
| 2021-11-24 | 1637683200           | 2021-11-24 00:06:00 | 1637683560               | 2021-06-11 00:00:00.123456789 | 1623340800                |
| 2021-11-23 | 1637596800           | 2021-11-23 00:07:00 | 1637597220               | 2021-07-11 00:00:00.123456789 | 1625932800                |
| 2021-11-22 | 1637510400           | 2021-11-22 00:08:00 | 1637510880               | 2021-08-11 00:00:00.123456789 | 1628611200                |
| 2021-11-21 | 1637424000           | 2021-11-21 00:09:00 | 1637424540               | 2021-09-11 00:00:00.123456789 | 1631289600                |
| 2021-11-20 | 1637337600           | 2021-11-20 00:10:00 | 1637338200               | 2021-10-11 00:00:00.123456789 | 1633881600                |
+------------+----------------------+---------------------+--------------------------+------------------------------+---------------------------+

WEEKDAY

Syntax
```
bigint weekday (datetime <date>)
```
Description
Returns a number that represents the day of the week in which a date value falls.
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:
- Monday is considered the first day of a week and its return value is 0. Days of a week are numbered in ascending order starting from 0. The return value of Sunday is 6.
- If the value of date is not of the DATETIME or STRING type or the format does not meet the requirements, an error is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 4. 
select weekday (datetime '2009-03-20 11:11:00');

-- The return value is 4. 
set odps.sql.type.system.odps2=false;
select weekday ('2009-03-20 11:11:00');

-- The return value is null. 
select weekday (null);

Examples of table data

Calculate the day of the week in which each date value in the datetime1 column falls. Data in Sample data is used in this example. Sample statement:

select datetime1, weekday(datetime1) as datetime1_weekday from mf_date_fun_t;

The following result is returned:

+---------------------+-------------------+
| datetime1           | datetime1_weekday |
+---------------------+-------------------+
| 2021-11-29 00:01:00 | 0                 |
| 2021-11-28 00:02:00 | 6                 |
| 2021-11-27 00:03:00 | 5                 |
| 2021-11-26 00:04:00 | 4                 |
| 2021-11-25 00:05:00 | 3                 |
| 2021-11-24 00:06:00 | 2                 |
| 2021-11-23 00:07:00 | 1                 |
| 2021-11-22 00:08:00 | 0                 |
| 2021-11-21 00:09:00 | 6                 |
| 2021-11-20 00:10:00 | 5                 |
+---------------------+-------------------+

WEEKOFYEAR

Syntax
```
bigint weekofyear (datetime <date>)
```
Description
Returns a number that represents the week of the year in which a date value falls. Monday is considered the first day of the week.
Note
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.
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.

Examples

Examples of 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 1 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);

Examples of table data

Calculate the nth 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                   |
+---------------------+----------------------+

YEAR

Syntax

int year(datetime|timestamp|date|string <date>)

Description
Returns the year in which a date value falls. This function is an additional function of MaxCompute V2.0.
Parameters
date: required. A date value of the DATETIME, TIMESTAMP, DATE, or STRING type. The input value is in the yyyy-mm-dd, yyyy-mm-dd hh:mi:ss, or yyyy-mm-dd hh:mi:ss:ff3 format. If the value is of the STRING type, the value must include at least the yyyy-mm-dd part and cannot contain extra strings.
Return value
A value of the INT type is returned. The return value varies based on the following rules:
- If the value of date is not of the DATETIME, TIMESTAMP, DATE, or STRING type or the format does not meet the requirements, null is returned.
- If the value of date is null, null is returned.

Examples

Examples of static data

-- The return value is 1970. 
select year('1970-01-01 12:30:00');

-- The return value is 1970. 
select year('1970-01-01');

-- The return value is 70. 
select year('70-01-01');

-- The return value is null. 
select year('1970/03/09'); 

-- The return value is null. 
select year(null);

Examples of table data

Obtain the year in which each date value in the date1, datetime1, timestamp1, and date3 columns falls. Data in Sample data is used in this example. Sample statement:

-- Enable the MaxCompute V2.0 data type edition. Commit the following SET statement together with SQL statements. 
set odps.sql.type.system.odps2=true;
select date1, year(date1) as date1_year, datetime1, year(datetime1) as datetime1_year, timestamp1, year(timestamp1) as timestamp1_year, date3, year(date3) as date3_year from mf_date_fun_t;

The following result is returned:

+------------+------------+---------------------+----------------+-------------------------------+-----------------+------------+------------+
| date1      | date1_year | datetime1           | datetime1_year | timestamp1                    | timestamp1_year | date3      | date3_year |
+------------+------------+---------------------+----------------+-------------------------------+-----------------+------------+------------+
| 2021-11-29 | 2021       | 2021-11-29 00:01:00 | 2021           | 2021-01-11 00:00:00.123456789 | 2021            | 2021-11-20 | 2021       |
| 2021-11-28 | 2021       | 2021-11-28 00:02:00 | 2021           | 2021-02-11 00:00:00.123456789 | 2021            | 2021-11-21 | 2021       |
| 2021-11-27 | 2021       | 2021-11-27 00:03:00 | 2021           | 2021-03-11 00:00:00.123456789 | 2021            | 2021-11-22 | 2021       |
| 2021-11-26 | 2021       | 2021-11-26 00:04:00 | 2021           | 2021-04-11 00:00:00.123456789 | 2021            | 2021-11-23 | 2021       |
| 2021-11-25 | 2021       | 2021-11-25 00:05:00 | 2021           | 2021-05-11 00:00:00.123456789 | 2021            | 2021-11-24 | 2021       |
| 2021-11-24 | 2021       | 2021-11-24 00:06:00 | 2021           | 2021-06-11 00:00:00.123456789 | 2021            | 2021-11-25 | 2021       |
| 2021-11-23 | 2021       | 2021-11-23 00:07:00 | 2021           | 2021-07-11 00:00:00.123456789 | 2021            | 2021-11-26 | 2021       |
| 2021-11-22 | 2021       | 2021-11-22 00:08:00 | 2021           | 2021-08-11 00:00:00.123456789 | 2021            | 2021-11-27 | 2021       |
| 2021-11-21 | 2021       | 2021-11-21 00:09:00 | 2021           | 2021-09-11 00:00:00.123456789 | 2021            | 2021-11-28 | 2021       |
| 2021-11-20 | 2021       | 2021-11-20 00:10:00 | 2021           | 2021-10-11 00:00:00.123456789 | 2021            | 2021-11-29 | 2021       |
+------------+------------+---------------------+----------------+-------------------------------+-----------------+------------+------------+

References

If the preceding built-in functions do not meet your business requirements, MaxCompute also supports user-defined functions (UDFs). For more information about UDFs, see Overview.
For more information about FAQ about MaxCompute SQL, see the following topics:
For more information about common errors and FAQ about built-in functions of MaxCompute, see the following topics:
- Common errors for built-in functions
- FAQ about built-in functions