Date/time/datetime functions

Read(382) Label: date, time,

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.

age()

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")

date()

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

datetime()

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

day()

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

dayname()

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

days()

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

deq()

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

elapse()

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

hour()

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

interval()

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

millisecond()

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

minute()

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

month()

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

now()

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

pdate()

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

second()

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

time()

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

year()

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