Expression: Date Functions

Today

Returns today’s date shifted by n days:

Today(n)

Today(0) is today’s date

Today(1) is tomorrow

Today(-1) is yesterday

Today(-7) is one week ago

AgeDays

Age of date in days relative to today:

AgeDays([Date])

If today is 30th May 2022 and date1 is 20th May 2022

AgeDays(date1) = 10

AgeWeeks

Age of date in whole weeks relative to today:

AgeWeeks([Booking Date])

If today is 30 May 2022 and date1 is 20 May 2022

AgeWeeks(date1) = 1

AgeMonths

Age of date in months relative to today:

AgeMonths([Booking Date])

If today is 30 May 2022 and date1 is 20 May 2021

AgeMonths (date1) = 12

AgeQuarters

Age of date in 3 calendar month periods relative to today:

AgeQuarters([Booking Date])

If today is 12 October 2021 and date1= 05 January 2021 AgeQuarters (date1) = 3

AgeYears

Age of date in years relative to today:

AgeYears([Booking Date])

If today is 30 May 2022 and date1= 20 May 2020

AgeYears (date1) = 2

DateDay

Day of date (1 to 31)

DateDay([Booking Date])

DateWeek

Week of date (1 to 53)

DateWeek([Booking Date])

DateMonth

Month of date 1=Jan, 2=Feb, … 12=Dec

DateMonth([Booking Date])

DateQuarter

Quarter of date 1=Q1, 2=Q2, 3=Q3, 4=Q4

DateQuarter([Booking Date])

DateYear

Year of date - e.g. 2005

DateYear([Booking Date])

DayOfWeek

Day of date 1=Monday, 2=Tuesday, … 7=Sunday

DayOfWeek([Booking Date])

DayOfYear

Calculates the day number in the year 1 to 366:

DayOfYear([date])

DayOfYear(20070105) = 5

BuildDate

The FastStats system build date shifted by n days:

BuildDate(n)

e.g. BuildDate (0) = build date

BuildDate (-1) = day before build date

DateDiff

Calculates [Date] - [Date] measured in the specified units (years, quarters, months, weeks, days):

DateDiff( [Date], [Date], "Units")

Units specifier is optional. If missing, the default units are days.

If only one date is specified, the given date is compared to today.

DateDiff([DepartureDate],[Booking Date],”Days”)

Is the number of days between departure and booking.

DateDiff([DepartureDate],20051231,”Days”)

Is the number of days between departure and 31 Dec 2005.

DateDiff([DepartureDate],Today(0),”Days”)

Is the number of days until departure.

DateDiff([DepartureDate],[Booking Date],”Weeks”)

Is the number of weeks between departure and booking.

The zero band extends in both a forward and backward direction from the reference date. An optional final parameter can be set to 1 to return missing value if the first date is after the second date. This ensures that results are only returned when there is a positive value, which corresponds to when the date parameters are in chronological order - for example:

DateShift

DateShift – shift a time by a number of units (years, quarters, months, weeks, days):

DateShift([Date],[Date],"Units")

DateShift([BookingDate],30,”Days”) calculates 30 days after booking.

DateShift([DepartureDate],-30,”Days”) calculates 30 days before departure.

DateShift([DOB],18,"Years") calculates 18th birthday

MakeDate

Makes a constant date from numbers:

MakeDate(Years, Months, Days)

MakeDate(2007,08,22)

MakeDate(DateYear([Booking Date],01,01) returns the start of the year in which the booking was made.

Returns number of identified days in month containing the date::

DaysInMonth (Date, [optional] pattern)

Pattern is a string indicating weekdays to be counted Monday to Sunday - for example:

"1111111" is all days (default)

"1111100" is all weekdays

"0000011" all weekend days, etc.

Shifts the date/datetime to the start of the unit and then offsets by the offset days:

DateStart(date,offset,units)

e.g. DateStart([Date],6,"month") => the first day in the month plus 6 days - i.e. the 7th of the month.

Shifts the date/datetime to the end of the unit and then offsets by the offset days:

DateEnd(date,offset,units)

e.g. DateEnd([Date],6,"month") => the last day in the month plus 6 days - i.e. the 6th of the next month.

Returns inclusive days between two dates with only days of the week in the pattern being selected:

SelectedDays(date,date,pattern)

e.g. SelectedDays([Date],[Date],"1111100") => Mondays-Fridays only counted.

Returns the specified next occurrence of this date:

DateNext(date,offset,units)

e.g. DateNext([Date],1,"years") => the next time this day of the year occurs.

Shifts the date/datetime to the start of the financial unit and then offsets by offset days:

FinancialDateStart(date,offset,units)

e.g. FinancialDateStart([Date],6,"month") => the first day in the financial month plus 6 days. i.e. the 7th day of the financial month.

Shifts the date/datetime to the end of the financial unit and then offsets by the offset days:

FinancialDateEnd(date,offset,units)

e.g FinancialDateEnd([Date],6,"month") => the last day in the financial month plus 6 days - i.e. the 6th of the next financial month.

IsWeekday(date) returns:

1 if the date is a weekday

0 if the date is not a weekday

A missing value is returned if not a date.

IsWeekend(date) returns:

1 if the date is a weekend

0 if the date is not a weekend

A missing value is returned if not a date.

Day

Number of days from year 0000 to date

Day([Booking Date])

Week

Number of weeks from year 0000 to date

Week([Booking Date])

Month

Number of months from year 0000 to date

Month([Booking Date])

Year

Number of years from year 0000 to date

Year([Booking Date])