This section introduces date/time/datetime functions used for report making by listing their descriptions, syntax, parameters, return values and options, and giving related examples.
Description:
Compute the number of whole years between the specified time and the current time.
Syntax:
age(dateExp{, formatExp })
age(stringExp,formatExp)
Parameter:
|
dateExp |
A date expression, whose result is a date |
|
stringExp |
An expression, whose result must be date or a string of the Chinse datetime format |
|
formatExp |
Format expression, such as "yyyyMMdd" and "yyyy-MM-dd" |
Note:
The function computes the number of whole years between the time specified in dateExp to the current time.
Return value:
Integer type
Option:
|
@y |
Accurate to year |
|
@m |
Accurate to month; accurate to day by default |
Example:
Example 1: age(date ("1980-09-01"))
Example 2: age@m(datetime("1980-09-01 12:23:56"))
Example 3: age@y("19800227","yyyyMMdd")
Description:
Convert a string or integers to date type data.
Syntax:
date(stringExp) Format of result returned by stringExp should be consistent with the application default date format "yyyy-MM-dd"; if the string contains a time part, the time will not be converted
date(year,month,day) Convert integers year, month and day to date type data
date(stringExp,format:loc) Convert stringExp to a date according to the specified format; parameter loc is the language stringExp uses; use the system default language when loc is absent
date(datetimeExp) Get the date part from datetime data
date(ym,day) Convert integers ym and day to date type data
Parameter:
|
stringExp |
A string expression |
|
format |
A format string |
|
loc |
Language name, which is case-insensitive; the most commonly-used languages are Chinese (zh) and English (en) |
|
year |
An integer |
|
month |
An integer |
|
day |
An integer |
|
datetimeExp |
Datetime data |
|
ym |
A 6-digit integer, which is the YearMonth |
|
day |
An integer |
Return value:
Date type
Example:
Example 1: date("1982-08-09") Return date 1982-08-09
Example 2: date("1982-08-09 10:20:30") Return date 1982-08-09
Example 3: date(1982,18,09) Return date 1983-06-09
Example 4: date(1982,08,09) Return date 1982-08-09
Example 5: date(1982,-8,09) Return date 1981-04-09
Example 6: date(now()) Return date 2016-09-22
Example 7: date("12/28/1972","MM/dd/yyyy") Return date 1972-12-28
Example 8: date(189208,08) Return date 1892-08-08
Example 9: date("4 Jul 2001","d MMM yyyy":"en") Return date 2001-07-04
Description:
Convert a string or a long integer to datetime data.
Syntax:
datetime(y,m,d,h,m,s) Convert integers y, m, d, h, m, s to datetime data
datetime(long) Convert long integer long to date data
datetime(string, format:loc) Convert string specified by parameter string to datetime data according to the specified format; without parameter format, format of string should be consistent with the application default datetime format "yyyy-MM-dd HH:mm:ss"; parameter loc is the language string uses; use the system default language when loc is absent
datetime(date,time) Concatenate date and time into datetime data
datetime(datetimeExp) Adjust datetimeExp’s accuracy and return it; accurate to day by default
datetime(ym,d,h,m,s) Convert integers ym, d, h, m, s to a datetime data
Parameter:
|
string |
A string |
|
long |
A long integer in millisecond |
|
format |
A datetime format |
|
loc |
Language name, which is case-insensitive; the most commonly-used languages are Chinese (zh) and English (en) |
|
date |
Date type data |
|
time |
Time type data |
|
y |
A positive integer, which is the year |
|
m |
A positive integer, which is the month |
|
ym |
A 6-digit positive integer, which is the YearMonth |
|
d |
A positive integer, which is the day |
|
h |
A positive integer, which is the hour |
|
m |
A positive integer, which is the minute |
|
s |
A positive integer, which is the second |
|
datetimeExp |
Datetime data |
Return value:
Datetime type
Option:
|
@m |
Accurate to minute for syntax datetime(datetimeExp) |
|
@s |
Accurate to second for syntax datetime(datetimeExp) |
Example:
Example 1: datetime("2006-01-01 10:20:30") Return 2006-01-01 10:20:30
Example 2: datetime("2006-01-01 10:20:30:111") Return 2006-01-01 10:20:30
Example 3: datetime(12345) Return 1970-01-01 08:00:12
Example 4: datetime(now()) Return 2016-09-22 00:00:00
Example 5: datetime(date("1982-08-09"),time("12:12:12")) Return 1982-08-09 12:12:12
Example 6: datetime(2006,01,01,-10,-20,30) Return 2005-12-31 13:40:30
Example 7: datetime("12/28/1972 10:23:43","MM/dd/yyyy HH:mm:ss") Return 1972-12-28 10:23:43
Example 8: datetime@m(now()) Return 2016-09-22 08:04:00
Example 9: datetime@s(now()) Return 2016-09-22 08:04:55
Example 10: datetime(200601,01,-10,-20,30) Return 2005-12-31 13:40:30
Example 11: datetime("4 May 2001 3:08 PM","d MMM yyyy h:mm a":"zh") Return 2001-05-04 15:08:00
Description:
Get the corresponding date in the current month according to the specified date type data.
Syntax:
day(dateExp)
Parameter:
|
dateExp |
An expression that must return a date or a string of Chinese datetime format |
Return value:
Integer type
Option:
|
@w |
Get ordinal number of the date in the date type data in a week; return 1 for Sunday, 2 for Saturday an so on; get the corresponding date in the current month by default |
Example:
Example 1: day(datetime ("19800227","yyyyMMdd")) Return 27
Example 2: day(datetime(12345)) Return 1
Example 3: day(datetime("2006-01-15 10:20:30")) Return 15
Example 4: day@w(datetime("19800227","yyyyMMdd")) Return 4
Example 5: day@w(datetime("2006-01-15 10:20:30")) Return 1
Description:
Get the corresponding weekday for the date in date type data.
Syntax:
dayname(dateExp)
Parameter:
|
dateExp |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Character type
Example:
Example 1: dayname(datetime("19800227","yyyyMMdd")) Return Wednesday
Example 2: dayname(datetime(12345)) Return Thursday
Example 3: dayname(datetime("2006-01-15 10:20:30")) Return Sunday
Example 4: dayname("12:14:34") Return Thursday
Example 5: dayname("1972-11-08") Return Wednesday
Example 6: dayname("1972-11-08 10:20:30") Return Wednesday
Description:
Get the number of days in the year, quarter or month to which the specified date belongs.
Syntax:
days(dateExp)
Parameter:
|
dateExp |
A date or a string of standard date/time/datetime format |
Return value:
Integer type
Option:
|
@q |
Get the number of days in the quarter where the specified date belongs |
|
@y |
Get the number of days in the year where the specified date belongs |
|
|
By default, get the number of days in the month where the specified date belongs |
Example:
Example 1: days(datetime("19800227","yyyyMMdd")) Return 29
Example 2: days(datetime("2006-01-15 10:20:30")) Return 31
Example 3: days@y(datetime("19800227","yyyyMMdd")) Return 366
Example 4: days@q(datetime("2006-01-15 10:20:30")) Return 90
Description:
Judge whether two dates are equal.
Syntax:
deq (datetimeExp1,datetimeExp2)
Parameter:
|
datetimeExp1 |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
|
datetimeExp2 |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Note:
Compare parameter dateExp1 and parameter dateExp2 to find whether they are equal.
Return value:
Boolean type
Option:
|
@y |
Accurate to year |
|
@q |
Accurate to quarter |
|
@m |
Accurate to month |
|
@t |
Accurate to ten days of a month |
|
@w |
Accurate to week |
|
|
By default, accurate to day |
Example:
Example 1: deq("1988-12-08","1988-12-07") Return false
Example 2: deq@y(date("1988-11-08"),date("1988-09-12")) Return true
Example 3: deq@m(date("1988-11-08"),date("1988-09-12")) Return false
Example 4: deq@q(date("1988-12-08"),date("1988-10-12")) Return true
Example 5: deq@t(date("1988-10-08"),date("1988-10-12")) Return false
Example 6: deq@w(date("1988-10-05"),date("1988-10-08")) Return true
Description:
Get the date/time which is a certain time period before or after the specified date/time/datetime data.
Syntax:
elapse (dateExp, n)
dateExp ± n elapse(dateExp, n)
Parameter:
|
dateExp |
A specified start date expression that should return a date or a string of standard date format |
|
n |
An integer expression according to which a date/time n days/years/months after or before the specified date/time/datetime is computed, depending on whether n is positive or negative |
Note:
The function gets a date n days/years/months ago/later according to parameter dateExp.
Get the same date on the same month for the specified date; if such a date doe not exist, return the last date in the same month.
Return value:
Datetime type
Option:
|
@y |
Get a date n years before or after the specified date |
|
@q |
Get a date n quarters before or after the specified date |
|
@m |
Get a date n months before or after the specified date |
|
@e |
If the specified date is the last day of a month, the new date will not be adjusted to the last day of the month to which it belongs. Without the option the new date will be automatically adjusted to the last day of the corresponding month. This option works with @yqm |
|
@s |
Get a date n seconds before or after the specified date |
|
@ms |
Get a date n milliseconds before or after the specified date |
|
|
By default, get a date n days before or after the specified date |
Example:
|
elapse(datetime("19800227","yyyyMMdd"),5) |
1980-03-03 00:00:00 |
|
elapse@s(datetime("19800227","yyyyMMdd"),5) |
1980-02-27 00:00:05 |
|
elapse@ms(datetime("19800227","yyyyMMdd"),5) |
1980-02-27 00:00:00 |
|
elapse(datetime("19800227","yyyyMMdd"),-3) |
1980-02-24 00:00:00 |
|
elapse@y(datetime("19800227","yyyyMMdd"),-3) |
1977-02-27 00:00:00 |
|
elapse@q(datetime("19800227","yyyyMMdd"),-3) |
1979-05-27 00:00:00 |
|
elapse@m(datetime("19800227","yyyyMMdd"),-3) |
1979-11-27 00:00:00 |
|
elapse@s(datetime("19800227","yyyyMMdd"),-3) |
1980-02-26 23:59:57 |
|
elapse@ms(datetime("19800227","yyyyMMdd"),-3) |
1980-02-26 23:59:59 |
|
datetime("19800227","yyyyMMdd")+5 |
1980-03-03 00:00:00 |
|
datetime("19800227","yyyyMMdd")-5 |
1980-02-22 00:00:00 |
|
elapse@y(datetime("19770228","yyyyMMdd"),3) |
1980-02-29 00:00:00 |
|
elapse@ey(datetime("19770228","yyyyMMdd"),3) |
1980-02-28 00:00:00 |
|
elapse@q(datetime("19800229","yyyyMMdd"),1) |
1980-05-31 00:00:00 |
|
elapse@eq(datetime("19800229","yyyyMMdd"),1) |
1980-05-29 0:00:00 |
Description:
Get the hour to which the specified datetime belongs.
Syntax:
hour(datetimeExp)
Parameter:
|
datetimeExp |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Integer type
Example:
Example 1: hour("1983-12-15") Return 0
Example 2: hour("1983-12-15 10:30:25") Return 10
Example 3: hour(datetime("2006-01-15 13:20:30")) Return 13
Description:
Compute the interval between two datetime values.
Syntax:
interval (datetimeExp1,datetimeExp2)
Parameter:
|
datetimeExp1 |
A date or a string of standard date/time/datetime format |
|
datetimeExp2 |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Integer type
Option:
|
@y |
Get the number of years between two datetime values |
|
@q |
Get the number of quarters between two datetime values |
|
@m |
Get the number of months between two datetime values |
|
@s |
Get the number of seconds between two datetime values |
|
@ms |
Get the number of milliseconds between two datetime values |
|
@r |
Compute the interval between two datetime values and return a real number |
|
|
By default, it computes the number of days between the two datetime values |
|
@w |
Get the number of weeks between two datetime values |
|
@7 |
Compute the number of Sundays between two datetime values according to a left-open interval |
|
@1 |
Compute the number of Mondays between two datetime values according to a left-open interval |
Example:
Example 1: interval(datetime("19800227","yyyyMMdd"),datetime("1983-02-27 00:00:45")) Return 1096
Example 2: interval@y(datetime("19800227","yyyyMMdd"),datetime("1983-02-27 00:00:45")) Return 3
Example 3: interval@q(datetime("19800227","yyyyMMdd"),datetime("1983-02-27 00:00:45")) Return 12
Example 4: interval@m(datetime("19800227","yyyyMMdd"),datetime("1983-02-27 00:00:45")) Return 36
Example 5: interval@s ("1972-11-08 10:20:30","1972-11-08 10:30:50") Return 620
Example 6: interval@ms(datetime("19800227","yyyyMMdd"),datetime("1980-02-27 00:00:45")) Return 45000
Example 7: interval@r("1972-11-08 10:20:30","1973-11-08 10:30:50") Return 365.00717592592594
Example 8: interval@w("1972-10-08 10:20:30","1972-11-08 10:30:50") Return 4
Example 9: interval@7("1972-10-08 10:20:30","1972-11-08 10:30:50") Return 4
Example 10: interval@1("1972-10-08 10:20:30","1972-11-08 10:30:50") Return 5
Description:
Get the millisecond part of the specified datetime data.
Syntax:
millisecond(datetimeExp)
Parameter:
|
datetimeExp |
A date or a string of standard date/time/datetime format |
Return value:
Integer type
Example:
Example 1: millisecond(datetime("1980-02-27 12:00:02:123 ","yyyy-MM-dd hh:mm:ss:SSS")) Return 123
Example 2: millisecond(now()) Return the millisecond of the current time
Description:
Get the minute part of the specified datetime data.
Syntax:
minute(datetimeExp)
Parameter:
|
datetimeExp |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Integer type
Example:
Example 1: minute(datetime ("19800227","yyyyMMdd")) Return 0
Example 2: minute("1972-11-08 10:20:30") Return 20
Example 3: minute(datetime("2006-01-15 13:20:30")) Return 20
Description:
Get the month part of the specified date/datetime data.
Syntax:
month(dateExp)
Parameter:
|
dateExp |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Integer type
Option:
|
@y |
Return a six-digit number when parameter dateExp contains the year |
Example:
Example 1: month(datetime ("19800227","yyyyMMdd")) Return 2
Example 2: month("1972-11-08 10:20:30") Return 11
Example 3: month(datetime("2006-01-15 13:20:30")) Return 1
Example 4: month@y("1972-11-08 10:20:30") Return 197211
Description:
Get the current system datetime, accurate to millisecond.
Syntax:
now()
Return value:
Datetime type
Option:
|
@d |
Return the date part only, date type |
|
@t |
Return the time part only, time type |
|
@m |
Accurrate to minute |
|
@s |
Accurate to second |
Example:
Example 1: now() The current system datetime, for example: 2016-12-15 11:26:33:422
Example 2: now@d() The current system datetime, for example: 2016-12-15
Example 3: now@t() The current system time, for example:11:26:33
Example 4: now@m() The current system datetime, for example: 2016-12-15 11:26:00:0
Example 5: now@s() The current system datetime, for example: 2016-12-15 11:26:33:0
Description:
Get the first day and the last day of the week/month/quarter to which a date belongs.
Syntax:
pdate(dateExp)
Parameter:
|
dateExp |
A date or a string of standard date format |
Return value:
Datetime type
Option:
|
@w |
Get Sunday of the week to which the specified date belongs |
|
@we |
Get Saturday of the week to which the specified date belongs |
|
@m |
Get the first day of the month to which the specified date belongs |
|
@me |
Get the last day of the month to which the specified date belongs |
|
@q |
Get the first day of the quarter to which the specified date belongs |
|
@qe |
Get the last day of the quarter to which the specified date belongs |
|
@y |
By default, get Sunday of the week to which the specified date belongs Get the first day of the year to which the specified data belongs |
Example:
Example 1: pdate@w(datetime("19800227","yyyyMMdd")) 1980-02-24
Example 2: pdate@we (datetime("19800227","yyyyMMdd")) 1980-03-01
Example 3: pdate@m(datetime("19800227","yyyyMMdd")) 1980-02-01
Example 4: pdate@me(datetime("19800227","yyyyMMdd")) 1980-02-29
Example 5: pdate@q(datetime("19800227","yyyyMMdd")) 1980-01-01
Example 6: pdate@qe(datetime ("19800227","yyyyMMdd")) 1980-03-31
Example 7: pdate@y(datetime("19800227","yyyyMMdd")) 1980-01-01
Description:
Get the second from specified datetime data.
Syntax:
second(datetimeExp)
Parameter:
|
datetimeExp |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Integer type
Example:
Example 1: second(datetime ("19800227","yyyyMMdd")) Return 0
Example 2: second("1972-11-08 10:20:30") Return 30
Example 3: second(datetime("2006-01-15 13:20:45")) Return 45
Description:
Convert a string or datetime data to time data.
Syntax:
time(stringExp{, format }:loc) Convert string specified by parameter stringExp to time data according to the specified format; without parameter format, format of stringExp should be consistent with the application default datetime format "yyyy-MM-dd HH:mm:ss"; parameter loc is the language string uses; use the system default language when loc is absent
time(h,m,s) Convert integers h, m, s to time data
time(datetimeExp) Get the time part of datetime data
Parameter:
|
stringExp |
A string |
|
datetimeExp |
Datetime data |
|
format |
The format string |
|
loc |
Language name, which is case-insensitive; the most commonly used languages are Chinese (zh) and English (en) |
|
h |
An integer |
|
m |
An integer |
|
s |
An integer |
Return value:
Time type
Option:
|
@s |
Accurate to second for syntax time(datetimeExp) |
|
@m |
Accurate to minute for syntax time(datetimeExp) |
Example:
Example 1: time("00:00:45") 00:00:45
Example 2: time@s(now()) 16:28:260
Example 3: time("00/00/45","HH/mm/ss") 00:00:45
Example 4: time(12,13,00) 12:13:00
Example 5: time(now()) 16:28:26512
Example 6: time@m(now()) 16:28:000
Example 7: time("1:08 PM","h:mm a":"en") 13:08:00
Description:
Get the year part of the specified date data.
Syntax:
year(dateExp)
Parameter:
|
dateExp |
A date or a string of standard date/time/datetime format, such as yyyy-MM-dd HH:mm:ss, yyyy-MM-dd or HH:mm:ss |
Return value:
Integer type
Example:
Example 1: year(datetime ("19800227","yyyyMMdd")) Return 1980
Example 2: year("1972-11-08 10:20:30") Return 1972
Example 3: year(datetime("2006-01-15 13:20:45")) Return 2006