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 2007 and date1 is 20th May 2007

AgeDays(date1) = 10

Age of date in 3 calendar month periods relative to today

AgeQuarters([Booking Date])

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

AgeWeeks

Age of date in whole weeks relative to today

AgeWeeks([Booking Date])

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

AgeWeeks(date1) = 1

AgeMonths

Age of date in months relative to today

AgeMonths([Booking Date])

If today is 30 May 2007 and date1 is 20 May 2006

AgeMonths (date1) = 12

AgeYears

Age of date in years relative to today

AgeYears([Booking Date])

If today is 30 May 2007 and date1= 20 May 2005

AgeYears (date1) = 2

DateDay

Day of date 1.. 31

DateDay([Booking Date])

DateWeek

Week of date 1..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])

Date2Days

The number of days since 0000

Date2Days(2006,01,01)

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:

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.

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

DayOfYear

Calculates the day number in the year 1 .. 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

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])