Date functions are useful when you want to compare data collected between two date periods. Date formulas allow you to apply date related functions to your formulas.

## Date formulas

The date formulas include:

`add_days`

Returns the result of adding the specified number of days to the given date.

Examples
```add_days (01/30/2015, 5) = 02/04/2015
`add_minutes`

Returns the result of adding the specified number of minutes to the given date, datetime, or time.

Examples
```add_minutes ( 01/30/2015 00:10:20 , 5 ) = 01/30/2015 00:15:20
add_minutes ( invoiced , 30 )```
`add_months`

Returns the result of adding the specified number of months to the given date.

Examples
```add_months ( 01/30/2015, 5 ) = 06/30/2015
add_months ( invoiced_date , 5 )```
`add_seconds`

Returns the result of adding the specified number of seconds to the given date/ datetime/ time.

Examples
```add_seconds ( 01/30/2015 00:00:00, 5 ) = 06/30/2015 00:00:05
add_seconds ( invoiced_date , 5 )```
`add_weeks`

Returns the result of adding the specified number of weeks to the given date.

Examples
```add_weeks ( 01/30/2015, 2 ) = 02/13/2015
add_weeks ( invoiced_date , 2 )```
`add_years`

Returns the result of adding the specified number of years to the given date.

Examples
```add_years ( 01/30/2015, 5 ) = 01/30/2020
add_years ( invoiced_date , 5 )```
`date`

Returns the date portion of a date.

Examples
`date (home visit)`
`day`

Returns the number (1-31) of the day of the month for a date.

Examples
```day (01/15/2014) = 15
day (date ordered)```
`day_number_of_quarter`

Returns the number of the day in a quarter for a date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'.

Examples
```day_number_of_quarter (01/30/2015) = 30
day_number_of_quarter (01/30/2015, 'fiscal') = 91  // May 1 is start of fiscal year```
`day_number_of_week`

Returns the number (1-7) of the day in a week for a date. Monday is 1, and Sunday is 7.

Examples
```day_number_of_week(01/15/2014) = 3
day_number_of_week (shipped)```
`day_number_of_year`

Returns the number (1-366) of the day in a year from a date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```day_number_of_year (01/30/2015) = 30
day_number_of_year ( 01/30/2015, 'fiscal' ) = 275 // May 1 is start of fiscal year
day_number_of_year (invoiced)```
`day_of_week`

Returns the day of the week for the given date.

Examples
```day_of week (01/30/2015) = Friday
day_of_week (serviced)```
`diff_days`

Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact.

Examples
```diff_days (01/15/2014, 01/17/2014) = -2
diff_days (purchased, shipped)```
`diff_time`

Subtracts the second date from the first date and returns the result in number of seconds.

Examples
```diff_time (01/30/2014, 01/31/2014) = -86,400
diff_time (clicked, submitted)```
`hour_of_day`

Returns the hour of the day for the given date.

Examples
`hour_of_day (received)`
`is_weekend`

Returns `true` if the date is a Saturday or a Sunday.

Examples
```is_weekend (01/31/2015) = true
is_weekend (emailed)```
`month`

Returns the month from the date.

Examples
```month (01/15/2014) = January
month (date ordered)```
`month_number`

Returns the number (1-12) of the month from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```month_number (09/20/2014) = 9
month_number ( 09/20/2014, 'fiscal' ) = 5 // May 1 is start of fiscal year
month_number (purchased)```
`month_number_of_quarter`

Returns the month (1-3) number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
`month_number_of_quarter (02/20/2018) = 2`

In the following example, May 1st is the start of the fiscal year.

`month_number_of_quarter (02/20/2018,'fiscal' ) = 1`
`now`

Returns the current date and time in your locale’s standard date and time format. For example, if your locale is English (United States), it returns `MM/dd/yyyy hh:mm:ss` (04/27/2022 12:34:00).

Examples
`now ()`
`quarter_number`

Returns the number (1-4) of the quarter associated with the date. You can add an optional second parameter to specify 'fiscal' or 'calendar' dates.

Default

'calendar'

Examples
```quarter_number ( 04/14/2014) = 2
quarter_number ( 04/14/2014, 'fiscal' ) = 4 // May 1 is start of fiscal year
quarter_number ( shipped )```
`start_of_month`

Returns `MMM yyyy` for the first day of the month. Your installation configuration can override this setting so that it returns a different format, such as `MM/dd/yyyy`. Speak with your ThoughtSpot administrator for information on doing this.

Examples
```start_of_month ( 01/31/2015 ) = Jan FY 2015
start_of_month (shipped)```
`start_of_quarter`

Returns the date for the first day of the quarter for the date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```start_of_quarter ( 04/30/2014) = Apr 2014
start_of_quarter ( 04/30/2014, 'fiscal') = Feb 2014    // May 1 is the start of the fiscal year
start_of_quarter (sold)```
`start_of_week`

Returns the date for the first day of the week for the given date.

Examples
```start_of_week ( 01/31/2020 ) = 01/27/2020
start_of_week (emailed)```
`start_of_year`

Returns the date for the first day of the year for the date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'.

Examples
```start_of_year (04/30/2014) returns Jan 2014
start_of_year (04/30/2014, 'fiscal')    // May 1 is start of fiscal year
start_of_year (joined)```
`time`

Returns the time portion of a date.

Examples
```time (1/31/2002 10:32) = 10:32
time (call began)```
`today`

Returns the current date in your locale’s standard date format. For example, if your locale is English (United States), it returns `MM/dd/yyyy` (04/27/2022).

Examples
`today ()`
`week_number_of_month`

Returns the week number for the date in a month.

Examples
`week_number_of_month(03/23/2017) = 3`
`week_number_of_quarter`

Returns the week number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```week_number_of_quarter (01/31/2020) = 5
week_number_of_quarter (05/31/2020, 'fiscal') = 5   // May 1 is start of fiscal year```
`week_number_of_year`

Returns the week number for the date in a year. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result.

Default

'calendar'

Examples
```week_number_of_year (01/17/2014) = 3
week_number_of_year ( 01/17/2014, 'fiscal') = 38.  // May 1 is start of fiscal year```
`year`

Returns the year from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'.

Examples
```year (01/15/2014) = 2014
year (12/15/2013, 'fiscal' ) = 2014.  // May 1 is start of fiscal year
year (date ordered)```

## Calculate date formulas

Calculating date formulas is useful when you want to compare data from different date periods. Here are some examples of using date formulas:

### Example 1

The following example shows you how to create formulas that you can use to compare data from this week to last week.

• The formula for this week is: `week ( today () ) - week (date)`

• The formula for last week is: `diff_days ( week ( today () ) ) , week ( date ) )`

### Example 2

The following example shows you how to calculate the percent increase from the last date period to this period in terms of revenue.

1. Create the formula: `this week revenue = sum ( if ( this week ) then revenue else 0 )`

2. Then create the formula: `last week revenue = sum ( if (last week ) then revenue else 0 )`

3. Use nested formulas to calculate the percent increase by creating a parent formula: `percent increase = ( ( this week revenue - last week revenue) / last week revenue ) * 100`

## Fiscal and Gregorian calendars

For the following date formulas, you can further specify either `fiscal` or Gregorian `calendar` on which to base date calculations. (If you do not specify a calendar type, the formula defaults to standard Gregorian, with the year starting in January.)

• `day_number_of_quarter`

• `day_number_of_year`

• `month_number`

• `month_number_of_quarter`

• `quarter_number`

• `start_of_quarter`

• `start_of_year`

• `week_number_of_quarter`

• `week_number_of_year`

• `year`

Your ThoughtSpot administrator and ThoughtSpot Support can set up a fiscal calendar year to start on any month. If the `fiscal` year is not explicitly configured in the system, `fiscal` defaults to January, the same as the Gregorian `calendar`.

For example, the formula `month_number_of_quarter (05/31/2014)` would return `2` based on the default Gregorian calendar, whereas the formula `month_number_of_quarter (05/31/2014, 'fiscal')` would return `1` if your administrator has configured the fiscal calendar to start in May.

Related information