Date and time functions

Date and time functions return date or time data. Note that you may need to convert the format of your source data in the application.

Table 1: Date and time functions

Function

Syntax

Description

AddMonthToDate(#date#,periods)

  • #date#: Original date
  • periods: Number of periods to add

Returns a date that is produced by adding a specified number of month(s) to a specified date.

For example:
AddMonthToDate(#2012-01-01#,1)
returns 2012-02-01

AddWeekToDate(#date#,periods)

  • #date#: Original date
  • periods: Number of periods to add

Returns a date that is produced by adding a specified number of week(s) to a specified date.

For example:
AddWeekToDate(#2012-01-01#,1)
returns 2012-01-08

AddYearToDate(#date#,periods)

  • #date#: Original date
  • periods: Number of periods to add

Returns a date that is produced by adding a specified number of year(s) to a specified date. Use negative numbers to remove a year.

For example:
AddYearToDate(#2012-01-01#,1)
returns 2013-01-01

CurrentDate()

Returns the current date as a date.

For example:
CurrentDate()
returns <CurrentDate>

DateDiffInDays(#start#,#end#)

  • #start#: Start date of the interval
  • #end#: End date of the interval

Returns the number of days between two dates.

For example:
DateDiffInDays(#2012-03-23#,#2012-01-30#)
returns -53

DateDiffInMonths(#start#,#end#)

  • #start#: Start date of the interval
  • #end#: End date of the interval

Returns the number of months between two specified dates.

For example:
DateDiffInMonths(#2013-02-01#,#2014-01-01#)
returns 11

Day(#date#)

#date#: A date

Returns the day of the month as a number from 1 to 31.

For example:
Day(#2012-03-23#)
returns 23

DayOfWeek(#date#)

#date#: A date

Returns the day of the week as a number from 1 (Sunday) to 7 (Saturday).

For example:
DayOfWeek(#2012-03-23#)
returns 6

DayOfYear(#date#)

#date#: A date

Returns the day of the year as a number.

For example:
DayOfYear(#2012-03-23#)
returns 83

LastDayOfMonth(#date#)

#date#: A date

Returns the date produced by computing the last day of the month of a specified date.

For example:
LastDayOfMonth(#2012-03-23#)
returns the date 2012-03-31

LastDayOfWeek(#date#)

#date#: A date

Returns the date produced by computing the last day of the week of a specified date.

For example:
LastDayOfWeek(#2012-03-23#)
returns the date 2012-03-24

MakeDate(year,month,day)

  • year: Number that represents a year
  • month: Number that represents a month
  • day: Number that represents a day of the month

Returns a date that is built from a specified year, month, and day.

For example:
MakeDate(2011,6,12)
returns the date 2011-06-12

Month(#date#)

#date#: A date

Returns the month of the year as a number from 1 to 12.

For example:
Month(#2012-03-23#)
returns 3

Quarter(#date#)

#date#: A date

Returns a number that represents the quarter of a specified date.

For example:
Quarter(#2012-03-23#)
returns 1

Week(#date#)

#date#: A date

Returns a number that represents the week of a specified date.

For example:
Week(#2012-03-23#)
returns 12

Year(#date#)

#date#: A date

Returns the year of a specified date.

For example:
Year(#2012-03-23#)
returns 2012