Date Functions

Date functions are available from the Date Function menu. They return the current date or operate on a date string. These functions are available every data provider except for raster, WFS, and WMS providers.

When you create an expression for geospatial features, you can use the following date functions.

Function Definition Syntax Example

ADDMONTHS

Finds the result of adding months to a date. The property value provided must have an integer value. Returns a string with a DateTime data type in the format of the original date value.

ADDMONTHS(Date_property, Number)

ADD_MONTHS(Start_Date,1)

This example adds one month to the value of Start_Date. If Start_Date is June 1, 2008, ADDMONTHS would return July 1, 2008.

CURRENTDATE

Returns the current date as a string with a DateTime data type.

CURRENTDATE( )

CURRENTDATE ( )

EXTRACT

Extracts a date/time field from a date/time value. Returns a value in the Gregorian calendar with a DateTime data type. The date/time field can be 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND'.

EXTRACT(field, Date_property)

EXTRACT(YEAR,Start_Date)

This example extracts the year from the value of Start_Date. If Start_Date is June 1, 2008, EXTRACT would return 2008.

MONTHSBETWEEN

Returns the number of months between two dates as a Double data type. If the first date is later than the second one, the result is positive. If the first date is earlier than the second one, the result is negative. If both dates are the same day of a month or are both the last day of a month, the result is an integer. Otherwise, it is the fractional portion of the result based on a 31-day month.

MONTHSBETWEEN(Date_property,Date_property)

MONTHSBETWEEN(Start_Date, End_Date)

In this example, if Start_Date is March 1, 2007 and End_Date is April 1, 2007, MONTHSBETWEEN would return 1.