DATERANGE returns all individual dates between the specified start and end dates.
The syntax for DATERANGE is:
DATERANGE ( start , end , format, timezone )
Parameters
| start | The start date | Text format or Unix time | scalar |
| end | The end date | Text format or Unix time | scalar |
| format | The date format | Text format | scalar |
| timezone (Optional) | The timezone | Region or offset | scalar |
DATERANGE examples
Let's look at DATERANGE examples.
The example below displays a range of dates with a specified start and end date, but without a specified format. The format does not need to be specified because the dates are input in the default format, MM/dd/yyyy.
DATERANGE ( 04/01/2016 , 04/07/2016 )
returns [04/01/2016, 04/02/2016, 04/03/2016, 04/04/2016, 04/05/2016, 04/06/2016, 04/07/2016]
The example below displays a range of dates with a specified format.
DATERANGE ( 4/1/16 , 4/7/16 , M/d/yy )
returns [4/1/16, 4/2/16, 4/3/16, 4/4/16, 4/5/16, 4/6/16, 4/7/16]
The example below uses DATE_ADD and TODAY in the start value and TODAY in the end value to display dates from six days prior to the current date. Both these functions return dates in Unix time. This means the input start and end values of DATERANGE are in Unix time. When the input values are in Unix time, format in DATERANGE must not be specified.
Note: The examples assume today is April 7, 2016.
DATERANGE( DATE_ADD( TODAY( ) , day , -6 ) , TODAY( ) )
returns Unix time values [459468800, 1459555200, 1459641600, 1459728000, 1459814400, 1459900800, 1459987200]
In order to convert the Unix time values to a different format, you need to wrap DATERANGE in DATEVALUE and specify the format in DATEVALUE. See example below.
DATEVALUE( DATERANGE( DATE_ADD( TODAY( ) , day , -6 ) , TODAY( ) ) , "M/dd/yy" )
returns [4/01/16, 4/02/16, 4/03/16, 4/04/16, 4/05/16, 4/06/16, 4/07/16]
For more information on DATEVALUE, see DATE and DATEVALUE functions.