Date functions

Command format:

datetime dateadd(datetime date, bigint delta, string datepart)

For example:

 if trans_date = 2005-02-28 00:00:00
dateadd(trans_date, 1, 'dd') = 2005-03-01 00:00:00
-- Add one day. The result is beyond the last day in February. The actual value is the first day of next month.
dateadd(trans_date, -1, 'dd') = 2005-02-27 00:00:00
-- Minus one day.
dateadd(trans_date, 20, 'mm') = 2006-10-28 00:00:00
-- Add 20 months. The month spillover is caused and the year is added ‘1’.
If trans_date = 2005-02-28 00:00:00, dateadd(transdate, 1, 'mm') = 2005-03-28 00:00:00
If trans_date = 2005-01-29 00:00:00, dateadd(transdate, 1, 'mm') = 2005-02-28 00:00:00
-- No 29th is in Feb. of 2005. The date is intercepted to the last day of current month.
 If trans_date = 2005-03-30 00:00:00, dateadd(transdate, -1, 'mm') = 2005-02-28 00:00:00

In MaxCompute SQL, the datetime type has no direct constant representation, the following usage is wrong:

select dateadd(2005-03-30 00:00:00, -1, 'mm') from tbl1;

If you must describe the datetime type constant, try the following methods:

select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm') from tbl1;
-- The String type constant is converted to datatime type by explicit conversion.

Command format:

bigint datediff(datetime date1, datetime date2, string datepart)

Parameter description:

• datet1, date2: Datetime type, minuend, meiosis. If the input is ‘string’, it is converted to ‘datetime’ by implicit conversion. If it is another data type, an exception indicated.
• datepart: a String type constant. The extensional date format is supported. If ‘datepart’ does not meet the specified format or is other data type, an exception is indicated.

For example:

If start = 2005-12-31 23:59:59, end = 2006-01-01 00:00:00:
    datediff(end, start, 'dd') = 1
    datediff(end, start, 'mm') = 1
    datediff(end, start, 'yyyy') = 1
    datediff(end, start, 'hh') = 1
    datediff(end, start, 'mi') = 1
    datediff(end, start, 'ss') = 1
    datediff('2013-05-31 13:00:00', '2013-05-31 12:30:00', 'ss') = 1800
    datediff('2013-05-31 13:00:00', '2013-05-31 12:30:00', 'mi') = 30
If start = 19:33:23. 234, end = 19:33:23. 250 .Dates with milliseconds do not belong to the standard datetime style, and cannot be converted implicitly directly.Explicit conversion is required here:
  
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') = 16

Command format:

bigint datepart(datetime date, string datepart)

Return value:

• date: Datetime type. If the input is ‘string’ type, it is converted to ‘datetime’ type. If it is another data type, an exception is indicated.
• datepart: String type constant. The extensional date format is supported. If ‘datepart’ does not meet the specified format or is other data type, an exception is indicated.

For example:

datepart('2013-06-08 01:10:00', 'yyyy') = 2013
datepart('2013-06-08 01:10:00', 'mm') = 6

Command format:

datetime datetrunc (datetime date, string datepart)

Parameter description:：

• date: Datetime type. If the input is ‘string’ type, it is converted to ‘datetime’ type. If it is another data type, an exception indicated.
• datepart: String type constant. The extensional date format is supported.  If ‘datepart’ does not meet the specified format or is other data type, an exception is indicated.

For example:

datetrunc('2011-12-07 16:28:46', 'yyyy') = 2011-01-01 00:00:00
datetrunc('2011-12-07 16:28:46', 'month') = 2011-12-01 00:00:00
datetrunc('2011-12-07 16:28:46', 'DD') = 2011-12-07 00:00:00

Command format:

datetime getdate()

Command format:

boolean isdate(string date, string format)

Parameter description:

• date: date value of String format. If the input is ‘bigint’, or ‘double’ or ‘datetime’, it is be converted to ‘string’ type. If it is another data type, an exception is indicated.
• format: a String type constant. The extensional date format is not supported.  If redundant format strings appear in ‘format’, then get the datatime value corresponding to the first format string, other strings are taken as separators.  For example,  isdate (‘1234-yyyy’, ‘yyyy-yyyy’) returns ‘TRUE’.

Command format:

datetime lastday(datetime date)

Command format:

datetime to_date(string date, string format)

Parameter description:

• date: String type, date value to be converted. If the input is ‘bigint’, or ‘double’ or ‘datetime’, it is converted to ‘string’ type by implicit conversion. If it is another data type or null, an exception is indicated.
• format: String type constant, date format.  If it is not a constant or is other data type, the exception is caused.  The field ‘format’ does not support extensional format and other characters are ignored as invalid characters in analysis process. 

  The parameter format contains ‘yyyy’ at least; otherwise the expecion is indicated. If redundant format strings appear in format, then get the datatime value corresponding to the first format string, other strings are taken as separators.  For example, to_date (‘1234-2234’, ‘yyyy-yyyy’)  returns ‘1234-01-01 00:00:00’.

  Format format: yyyy is a four-digit year, mm is a two-digit month, DD is a two-digit day, HH is a 24-hour system, MI is a two-digit minute, SS is a two-digit second, FF3 is a three-digit precision millisecond.

For example:

to_date('Alibaba2010-12*03', 'Alibabayyyy-mm*dd') = 2010-12-03 00:00:00
to_date('20080718', 'yyyymmdd') = 2008-07-18 00:00:00
to_date('200807182030','yyyymmddhhmi')=2008-07-18 20:30:00
to_date('2008718', 'yyyymmdd') = null
-- The format does not meet the requirements. An exception is thrown.
to_date('Alibaba2010-12*3', 'Alibabayyyy-mm*dd') = null
-- Format is not compatible and exception is thrown.
to_date('2010-24-01', 'yyyy') = null
-- Format is not compatible and exception is thrown.
to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3')=2018-10-30 15:13:12

Command format:

string to_char(datetime date, string format)

Parameter description:

• date: Datetime type, the date value to be converted. If the input is ‘string’ type, it is converted to ‘datetime’ type by implicit conversion. If it is another data type, an exception indicated.
• format: String type constant.  If it is not a constant or is other data type, the exception is indicated.  In ‘format’, the date format part is replaced with the corresponding data and other characters are output directly.

For example:

to_char('2010-12-03 00:00:00', 'Alibabayyyy-mm*dd') = 'Alibaba2010-12*03'
to_char('2008-07-18 00:00:00', 'yyyymmdd') = '20080718' 
to_char('Alibaba2010-12*3', 'Alibabayyyy-mm*dd') -- Format is not compatible and exception is thrown.
to_char('2010-24-01', 'yyyy') -- Format is not compatible and exception is thrown.
to_char('2008718', 'yyyymmdd') -- Format is not compatible and exception is thrown.

Command format:

bigint unix_timestamp(datetime date)

Command format:

datetime from_unixtime(bigint unixtime)

Datetime type date value. If ‘unixtime’ is NULL, return NULL.

For example:

from_unixtime(123456789) = 1973-11-30 05:33:09

Command format:

bigint weekday(datetime date)

Command format:

bigint weekofyear(datetime date)

Return the nth week of a year which the date is included in.  Monday is taken as the first day of a week.

For example:

select weekofyear(to_date("20141229", "yyyymmdd")) from dual;  
Result:  
+------------+
| _c0 |
+------------+
| 1 |
+------------+
 -Although 20141229 belongs to 2014, most of the dates of the week are in 2015, therefore, the return result is 1, indicating that it is the first week of 2015.    
 select weekofyear(to_date("20141231", "yyyymmdd")) from dual；
-- Return 1.  
 select weekofyear(to_date("20141229", "yyyymmdd")) from dual；
-- Return 53.

With the upgraded version of MaxCompute 2.0, some new date functions are added to the product.  If the functions are used to design a new data type compatible with the Hive mode, you must add the following two set statements before the SQL statement of the new functions:

set odps.sql.type.system.odps2=true;--Enable the new type.

If you want to submit both at the same time, run the following statements:

set odps.sql.type.system.odps2=true;
select year('1970-01-01 12:30:00')=1970 from dual;

Command format:

INT year(string date)

For example:

year('1970-01-01 12:30:00') = 1970
year('1970-01-01') = 1970
year('70-01-01') = 70
year(1970-01-01) = null
year('1970/03/09') = null
year(null) Returns an exception

Command format:

INT quarter(datetime/timestamp/string date )

For example:

quarter('1970-11-12 10:00:00') = 4
quarter('1970-11-12') = 4

Command format:

INT month(string date)

For example:

month('2014-09-01') = 9
month('20140901') = null

Command format:

INT day(string date)

For example:

day('2014-09-01') = 1
day('20140901') = null

Command format:

INT dayofmonth(date)

For example:

dayofmonth('2014-09-01') = 1
dayofmonth('20140901') = null

Command format:

INT hour(string date)

For example:

hour('2014-09-01 12:00:00')=12
hour('12:00:00')=12
hour('20140901120000')=null

Command format:

INT minute(string date)

For example:

minute('2014-09-01 12:30:00') = 30
minute('12:30:00') = 30
minute('20140901120000') = null

Command format:

INT second(string date)

For example:

second('2014-09-01 12:30:45') = 45
second('12:30:45') = 45
second('20140901123045') = null

Command format:

timestamp current_timestamp()

For example:

select current_timestamp() from dual;--Returns '2017-08-03 11:50:30.661'

Command format:

string add_months(string startdate, int nummonths)

Parameter description:

• startdate: String-type value. The format must at least include the ‘yyyy-mm-dd’ date. Otherwise, null is returned.
• num_months: Int-type value.

For example:

Add_months ('2017-02-14', 3) = '2017-05-14'
add_months('17-2-14',3) = '0017-05-14'
add_months('2017-02-14 21:30:00',3) = '2017-05-14'
add_months('20170214',3) = null

Command format:

string last_day(string date)

For example:

last_day('2017-03-04') = '2017-03-31'
last_day('2017-07-04 11:40:00') = '2017-07-31'
last_day('20170304') = null

Command format:

string next_day(string startdate, string week)

Parameter description:

• startdate: String type, with the format ‘yyyy-MM-dd HH:mi:ss’ or  ‘yyyy-MM-dd’.
• week: String type, the first two or three letters of a day of the week, or the full name of the day of the week. For example: Mo, TUE, or FRIDAY.

For example:

next_day('2017-08-01','TU') = '2017-08-08'
next_day('2017-08-01 23:34:00','TU') = '2017-08-08'
Next_day ('20170801 ', 'tu') = NULL

Command format:

double months_between(datetime/timestamp/string date1, datetime/timestamp/string date2)

Parameter description:

• date1: Datetime, Timestamp, or String type, with the format ‘yyyy-MM-dd  HH:mi:ss’ or ‘yyyy-MM-dd’.
• date2: Datetime, Timestamp, or String type, with the format ‘yyyy-MM-dd  HH:mi:ss’ or ‘yyyy-MM-dd’.

Returns the Double type.

• When date1 is later than date2, the returned value is positive. When date2 is later than date1, the returned value is negative.
• When date1 and date2 correspond to the last days of two months, the returned value is an integer representing the number of months. Otherwise, the formula is (date1 - date2)/31.

Examples: 

months_between('1997-02-28 10:30:00', '1996-10-30') = 3.9495967741935485
months_between('1996-10-30','1997-02-28 10:30:00' ) = -3.9495967741935485
months_between('1996-09-30','1996-12-31') = -3.0