# Relative date variable functions

You can perform functions on date variables that will allow you to customize the date returned to fit your needs.

Multiple functions can be chained together; see examples below.

To try these out in Chartio, open the Explore tab in SQL Mode and add ‘SELECT’ before your date formula. For example:

SELECT {CURRENT_CALENDAR_YEAR.START.SUB(1, ‘day’)}

## Units

Units are used to perform calculations on dates (add or subtract), and extract part of a date (for example, return only the year part of a date). They can be used in either singular or plural form, and include:

• day
• week
• month
• year
• quarter (PART() only)

Add/subtract from relative date variables to create other dates. Use the following format:

``````{DATE_VARIABLE.ADD(integer, 'units')}
{DATE_VARIABLE.SUB(integer, 'units')}
``````

For example, to get the first day of the previous month:

``````{CURRENT_MONTH.START.SUB(1, 'month')}
``````

## Calculating the last day of the previous month

Let’s say it’s currently June. {CURRENT_MONTH.END.SUB(1, ‘month’)} will return 2016-05-30, because we’re subtracting one month from 2016-06-30.

To get the last day of the previous month, use the following instead:

{CURRENT_MONTH.START.SUB(1, ‘day’)}

## Extract part of a date

We have two functions for extracting part of a date: PART() and BUCKET(). PART() returns a number, and BUCKET() returns a date string.

### PART()

PART() allows you to extract part of a date, and returns it as a number that can be used in calculations. Use the following format:

``````{DATE_VARIABLE.PART('units')}
``````

#### PART() examples

Return the quarter number of the current day:

``````{TODAY.PART('quarter')}
``````

Return the last day of the current month:

``````{CURRENT_MONTH.END.PART('day')}
``````

Return the current year in number form:

``````{TODAY.PART('year')}
``````

### BUCKET()

BUCKET() allows you to extract part of a date, and returns it as a date string. Use the following format:

``````{DATE_VARIABLE.BUCKET('units')}
``````

#### BUCKET() examples

Return the month and year of the previous month:

``````{CURRENT_MONTH.START.SUB(1, 'month').BUCKET('month')}
``````

Return the ISO week date of the current day:

``````{TODAY.BUCKET('week')}
``````

Return the quarter date of the current day:

``````{TODAY.BUCKET('quarter')}
``````