Overview
When dealing with date-type data, you often need to perform operations such as getting the current date, the current time, and the year, month, and day of a date.
This document introduces common date functions in FineBI.
Getting Specified Date and Time
Function | Formula | Result |
Getting the current time | ||
Returns the current date (year-month-day). | TODAY() | If the system date is November 11, 2011, the formula returns 2011-11-11. |
Returns the current time (hour:minute:second) | NOW() | Returns 2012-06-20 14:55:57 if the cell format is general and the system time is 14:55:57, June 20, 2012. |
Getting the Year, Month, or Day of a Date | ||
Returns the year of a date. | YEAR("2011-11-11") | Returns 2011. |
Returns the month of a date. | MONTH("2011-11-11") | Returns 11. |
Returns the day of a date. | DAY("2011-11-11") | Returns 11. |
Getting a Specified Day of a Year, Quarter, Month, or Week | ||
Returns the date of yesterday. | NOW()-1 | Returns 2021-11-08 11:43:53 if the system time is 11:43:53, November 9, 2021. |
Returns the first day of a year. | DATE(YEAR(NOW()),01,01) | Returns 2011-01-01 if the system date is 2011-11-11. |
Returns the last day of a month. | DATE(YEAR(TODAY()),MONTH(TODAY()),DAYSOFMONTH(TODAY())) | Returns 2011-11-30 if the system date is 2011-11-11. |
Returns the number of days of the current month. | DAYSOFMONTH(NOW()) | Returns 30 if the system date is 2021-11-11. |
Returns the number of days from the current date to a specified date. | DATEDIF(DATE(YEAR(Date),MONTH(Date),DAY(Date)),TODAY(),"D") | Returns 29 if the specified date is 2021-10-10 and the system date is 2021-11-09. |
Returns the week number of the current date of the year. | WEEK(TODAY()) | Returns 41 if the system date is 2021-10-10. |
Getting a Specified Time Relative to the Current Month | ||
Returns the current month. | MONTH(TODAY()) | Returns 11 if the system date is November 11, 2011. |
Returns the same day last year. | YEARDELTA(TODAY(),-1) | Returns 2010-11-11 if the system date is November 11, 2011. |
Returns the month before last. | DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())) | Returns 2011-09-11 if the system date is November 11, 2011. |
Returns the last month. | DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) | Returns 2011-10-11 if the system date is November 11, 2011. |
Returns the date one year and one month before the current date. | DATE(YEAR(TODAY())-1,MONTH(TODAY())-1,DAY(TODAY())) | Returns 2010-10-11 if the system date is 2011-11-11. |
Returns the end of the current month. | DATEDELTA(DATE(YEAR(MONTHDELTA(Date,1)),MONTH(MONTHDELTA(Date,1)),1),-1) or DATE(YEAR(Date),MONTH(Date)+1,1-1) | Returns 2011-11-30 if the system date is 2011-11-11. |
Returns the maximum time in the current date dimension.  Note: | FORMAT(TODATE(MAX_AGG(DATETONUMBER(Date))),"YYYY-MM-dd HH:mm:ss") |
|
Time Conversion
Function | Formula | Result |
Data Type Conversion | ||
Converts the character string of the date type to the time type. TODATE() can convert any date-type data to a time type yyyy-MM-dd. | TODATE("2011/11/11") | If the system date is November 11, 2011, the formula returns 2011-11-11. |
Converts time-type data to date type in other formats. FORMAT() | FORMAT("2/15/2011","dd/MM/yyyy") | Returns 15/02/2011. |
Addition and Subtraction of Dates
You can use DATEDELTA(), MONTHDELTA(), and YEARDELTA() to perform addition and subtraction on dates.
Function | Formula | Result |
Data Type Conversion | ||
Adds and subtracts the date. | DATEDELTA(TODAY(),-1) Note: | Returns the date one day before the current date. For example, if the system date is 2011-11-11, returns 2011-11-10. |
Adds and subtracts the month. | MONTHDELTA(TODAY(),-1) | Returns the date one month before the current date. For example, if the system date is November 11, 2011, returns 2011-10-11. |
Adds and subtracts the year. | YEARDELTA(TODAY(),-1) | Returns the date one year before the current date. If the system date is November 11, 2011, returns 2010-11-11. |
