Date and Time Functions
  • 31 May 2022
  • 1 Minute to read
  • Contributors
  • Dark
    Light
  • PDF

Date and Time Functions

  • Dark
    Light
  • PDF

You can use date and time functions in custom tables and fields. See Adding a New Custom Table and Defining and Editing Field Formula for further details.

For a short video overview of the date and time functions, see below:

Following are descriptions for the date and time functions:

ADDYEARS(datetime, number)
ADDQUARTERS(datetime, number)
ADDMONTHS(datetime, number)
ADDDAYS(datetime, number)
ADDHOURS(datetime, number)
ADDMINUTES(datetime, number)
ADDSECONDS(datetime, number)

Adds a given number of years|quarters|months|days|hours|minutes|seconds to a specified date/time. An example of when to use this is when the fiscal year is not the same as the Gregorian calendar’s beginning and end dates.

CREATEDATE(year,month,day)
Creates a timestamp from a given year, month and day. Time is set to midnight.

CURRENTDATE()
Returns the current date.

CURRENTTIME()
Returns the current time.

CURRENTTIMESTAMP()
Returns the current timestamp.

GETYEAR(datetime)
GETQUARTER(datetime)
GETMONTH(datetime)
GETDAY(datetime)
GETHOUR(datetime)
GETMINUTE(datetime)
GETSECOND(datetime)

Returns a number that represents the year|quarter|month|day|hour|minute|second in a given date/time.

DAYOFWEEK(datetime)
Returns the day, out of seven days in a week, represented by a given date/time.

DAYOFYEAR(datetime)
Returns the day, out of 365 days in a year, represented by a given date/time.

WEEKOFYEAR(datetime)
Returns the week, out of 52 weeks in a year, represented by a given date/time.

Note

The WEEKOFYEAR function does not take into consideration the setting for First Day of Week. It always assumes that the week starts on Monday.

DAYNAME(number)
Returns the name of the day represented by the given number.

YEARDIFF(end,start)
QUARTERDIFF(end,start)
MONTHDIFF(end,start)
DAYDIFF(end,start)
HOURDIFF(end,start)
MINUTEDIFF(end,start)
SECONDDIFF(end,start)

Returns the difference in years|quarters|months|days|hours|minutes|seconds

TODATETIME()
Converts a specified value to a DateTime value. Sisense supports the string format: YYYY-MM-DD HH:mm:ss:ms and YYYY-MM-DD HH:mm:ss. If not HH:mm:ss are entered, Sisense assumes that the time is 12:00AM.


Was this article helpful?