All the Questions in this page use the below sample Dataset. It is the Orders Data for a fictitious seller.

Order NumberOrder DateShipping DateProductPrice ($)Profit ($)
000101 Jan 2018 08:30:01 AM03 Jan 2018 11:21:05 AMABC800150
000202 Jan 2018 01:15:25 PM05 Jan 2018 05:30:15 PMXYZ600120
..................

YEAR

Synonyms-
SyntaxYEAR(date)
DescriptionReturns the Year value of the Argument as a Number.
Argumentsdate: The Date in which the Year needs to be identified.
Examples
  • At Row-level, it returns the Year value of the Argument as a Number.
    e.g. TOTAL SALES WHERE YEAR(ORDER DATE) > 2015
  • When aggregated across a group of Rows, it returns the Average Year-value of the Argument within the group.
    e.g. YEAR(ORDER DATE) IN PRODUCTS
  • When used as a Dimension, it groups the Rows into Years.
    e.g. TOTAL SALES IN YEAR(ORDER DATE)
  • You can use the Keyword NOW to refer to the current Date.
    e.g. YEAR(NOW)

MONTH

Synonyms-
SyntaxMONTH(date)
DescriptionReturns the Month value of the Argument as a Number. A value between 1 to 12 for Jan to Dec.
Argumentsdate: The Date in which the Month needs to be identified.
Examples
  • At Row-level, it returns the Month value of the Argument as a Number.
    e.g. TOTAL SALES WHERE MONTH(ORDER DATE) > 7
  • When aggregated across a group of Rows, it returns the Average Month-value of the Argument within the group.
    e.g. MONTH(ORDER DATE) IN PRODUCTS
  • When used as a Dimension, it groups the Rows into Months: Jan, Feb, ..., Dec.
    e.g. TOTAL SALES IN MONTH(ORDER DATE)
  • You can use the Keyword NOW to refer to the current Date.
    e.g. MONTH(NOW)

WEEK

Synonyms-
SyntaxWEEK(date)
DescriptionReturns the Week-number of the Argument. Since there can be just above 52 weeks in a year, this will be a value between 1 and 53.
Argumentsdate: The Date in which the Week-number needs to be identified.
Examples
  • At Row-level, it returns the Week-number of the Argument.
    e.g. TOTAL SALES WHERE WEEK(ORDER DATE) > 34
  • When aggregated across a group of Rows, it returns the Average Week-value of the Argument within the group.
    e.g. WEEK(ORDER DATE) IN PRODUCTS
  • When used as a Dimension, it groups the Rows into Weeks: 1,2,...,53.
    e.g. TOTAL SALES IN WEEK(ORDER DATE)
  • You can use the Keyword NOW to refer to the current Date.
    e.g. WEEK(NOW)

DAY

Synonyms-
SyntaxDAY(date)
DescriptionReturns the Day-number of the Argument. A value between 1 to 7 for Mon to Sun. The starting day can be configured in the Data-Model.
Argumentsdate: The Date in which the Day-number needs to be identified.
Examples
  • At Row-level, it returns the Day-number of the Argument.
    e.g. TOTAL SALES WHERE DAY(ORDER DATE) > 3
  • When aggregated across a group of Rows, it returns the Average Day-value of the Argument within the group.
    e.g. DAY(ORDER DATE) IN PRODUCTS
  • When used as a Dimension, it groups the Rows into Days: Mon, Tue, ..., Sun.
    e.g. TOTAL SALES IN DAY(ORDER DATE)
  • You can use the Keyword NOW to refer to the current Date.
    e.g. DAY(NOW)

DATE

Synonyms-
SyntaxDATE(date)
DescriptionReturns the Date-number of the Argument. A value between 1 and 31.
Argumentsdate: The Date in which the Date-number needs to be identified.
Examples
  • At Row-level, it returns the Date-number of the Argument.
    e.g. TOTAL SALES WHERE DATE(ORDER DATE) > 12
  • When aggregated across a group of Rows, it returns the Average Date-value of the Argument within the group.
    e.g. DATE(ORDER DATE) IN PRODUCTS
  • When used as a Dimension, it groups the Rows into Dates: 1,2,..., 31.
    e.g. TOTAL SALES IN DATE(ORDER DATE)
  • You can use the Keyword NOW to refer to the current Date.
    e.g. DATE(NOW)

HOUR

Synonyms-
SyntaxHOUR(date)
DescriptionReturns the Hour-number of the Argument. A value between 0 and 23.
Argumentsdate: The Date in which the Hour-number needs to be identified.
Examples
  • At Row-level, it returns the Hour-number of the Argument.
    e.g. TOTAL SALES WHERE HOUR(ORDER DATE) > 10
  • When aggregated across a group of Rows, it returns the Average Hour-value of the Argument within the group.
    e.g. HOUR(ORDER DATE) IN PRODUCTS
  • When used as a Dimension, it groups the Rows into Hours: 0,1...23.
    e.g. TOTAL SALES IN HOUR(ORDER DATE)
  • You can use the Keyword NOW to refer to the current Date.
    e.g. HOUR(NOW)