Date and Time Functions (DateUtils.xml)

This provides some additional functions for working with Dates and to a certain extent Times.

Unless specified, the functions listed below will be added to a new Date category.

Unit tests for all these functions in DateUtils.Test folder.

MakeDate

Syntax: MAKEDATE(Year, Month, Day)

Examples:

• MAKEDATE(2010, 1, 2) returns '2010-01-02'

Creates a date from numerical inputs of Year, Month and Day.

• Please note no sanity checking of the numerical inputs.

MakeTime

Added to Time category

Syntax: MAKETIME(Hour, Minute, Second)

Examples:

• MAKETIME(12, 34, 56) returns '12:34:56'

Creates a time from numerical inputs of Hour, Minute and Second.

• Please note no sanity checking of the numerical inputs.

MakeDateTime

Added to DateTime category

Syntax: MAKEDATETIME(Year, Month, Day, Hour, Minute, Second)

Examples:

• MAKEDATETIME(2010, 1, 2, 12, 34, 56) returns '2010-01-02 12:34:56'

Creates a date and time from numerical inputs of Year, Month, Day, Hour, Minute and Second.

• Please note no sanity checking of the numerical inputs.

ToDate

Syntax: TODATE(DateTime)

Examples:

• TODATE('2010-12-31') returns 2010-12-31
• TODATE('2010-12-31 11:22:33') returns 2010-12-31

Truncates a DateTime value to a Date

• Only sanity checking is on the length of the field past. If not 10 (a Date) or 19 (a DateTime), returns NULL.

ToDateTime

Syntax: TODATETIME(Date)

Examples:

• TODATETIME('2010-12-31') returns 2010-12-31 00:00:00
• TODATETIME('2010-12-31 11:22:33') returns 2010-12-31 11:22:33

Appends Midnight to a Date to create a DateTime value

• Only sanity checking is on the length of the field past. If not 10 (a Date) or 19 (a DateTime), returns NULL.

DateFromMDY

Syntax: DATEFROMMDY(MDYString)

Examples:

• DATEFROMMDY('7/2/17') returns 2017-02-07

Parse a string in Month Day Year format to a Date (copes without leading 0s and different separators)

DateFromDMY

Syntax: DATEFROMDMY(DMYString)

Examples:

• DATEFROMDMY('7/2/17') returns 2017-07-02

Parse a string in Day Month Year format to a Date (copes without leading 0s and different separators)

Day

Syntax: DAY(DateTime)

Examples:

• DAY('2010-12-31') returns 31
• DAY('2010-12-31 11:22:33') returns 31

Returns the day of the month as a number (1 - 31).

Month

Syntax: MONTH(DateTime)

Examples:

• MONTH('2010-12-31') returns 12
• MONTH('2010-12-31 11:22:33') returns 12

Returns the month as a number (1 - 12).

Year

Syntax: YEAR(DateTime)

Examples:

• YEAR('2010-12-31') returns 2010
• YEAR('2010-12-31 11:22:33') returns 2010

Returns the four digit year as number.

WeekDay

Syntax: WEEKDAY(DateTime)

Examples:

• WEEKDAY('2010-12-31') returns 5 (Friday)
• WEEKDAY('2010-12-31 11:22:33') returns 5 (Friday)
• WEEKDAY('2012-01-01') returns 0 (Sunday)

Returns the day of the week represented as a number: 0 - Sunday through to 6 - Saturday.

Quarter

Syntax: QUARTER(DateTime)

Examples:

• QUARTER('2010-12-31') returns 4
• QUARTER('2010-12-31 11:22:33') returns 4
• QUARTER('2010-01-31') returns 1

Returns the quarter of the date as a number (1 - 4).

OrdinalDay

Syntax: ORDINALDAY(DateTime)

Examples:

• ORDINALDAY('2010-12-31') returns 31
• ORDINALDAY('2010-12-31 11:22:33') returns 31

Returns the day of the year as a number (1 - 366).

WeekStart

Syntax: WEEKSTART(DateTime)

Examples:

• WEEKSTART('2010-12-31') returns 2010-12-26
• WEEKSTART('2010-12-31 11:22:33') returns 2010-12-26
• WEEKSTART('2012-01-01') returns 2012-01-01

Returns the nearest Sunday less than or equal to DateTime.

WeekEnd

Syntax: WEEKEND(DateTime)

Examples:

• WEEKEND('2010-12-31') returns 2011-01-01
• WEEKEND('2010-12-31 11:22:33') returns 2011-01-01
• WEEKEND('2012-01-01') returns 2012-01-07

Returns the nearest Saturday greater than or equal to DateTime.

MonthStart

Syntax: MONTHSTART(DateTime)

Examples:

• MONTHSTART('2010-12-31') returns 2010-12-01
• MONTHSTART('2010-12-31 11:22:33') returns 2010-12-01
• MONTHSTART('2012-01-01') returns 2012-01-01

Returns the first day of the month of DateTime.

MonthEnd

Syntax: MONTHEND(DateTime)

Examples:

• MONTHEND('2010-12-31') returns 2010-12-01
• MONTHEND('2010-12-31 11:22:33') returns 2010-12-01
• MONTHEND('2012-01-01') returns 2012-01-01

Returns the last day of the month of DateTime.

QuarterStart

Syntax: QUARTERSTART(DateTime)

Examples:

• QUARTERSTART('2010-12-31') returns 2010-01-01
• QUARTERSTART('2010-12-31 11:22:33') returns 2010-01-01
• QUARTERSTART('2012-01-01') returns 2012-01-01

Returns the first day of the quarter of DateTime.

QuarterEnd

Syntax: QUARTEREND(DateTime)

Examples:

• QUARTEREND('2010-12-31') returns 2010-12-31
• QUARTEREND('2010-12-31 11:22:33') returns 2010-12-31
• QUARTEREND('2012-01-01') returns 2012-12-31

Returns the last day of the quarter of DateTime.

YearStart

Syntax: YEARSTART(DateTime)

Examples:

• YEARSTART('2010-12-31') returns 2010-01-01
• YEARSTART('2010-12-31 11:22:33') returns 2010-01-01
• YEARSTART('2012-01-01') returns 2012-01-01

Returns the first day of the year of DateTime.

YearEnd

Syntax: YEAREND(DateTime)

Examples:

• YEAREND('2010-12-31') returns 2010-12-31
• YEAREND('2010-12-31 11:22:33') returns 2010-12-31
• YEAREND('2012-01-01') returns 2012-12-31

Returns the last day of the year of DateTime.

DateAdd

Syntax: DATEADD(Date, Interval, Units)

Examples:

• DATEADD('2010-12-31', 1, 'days') returns '2011-01-31'
• DATEADD('2010-12-31 11:22:33', 1, 'days') returns '2011-01-31'

Equivalent to DateTimeAdd but returning result as a Date.

• Only the result is truncated to a Date value, input is passed straight through to DateTimeAdd function.

BusinessDays

Syntax: BUSINESSDAYS(StartDate, EndDate)

Examples:

• BUSINESSDAYS('2016-05-06','2016-05-13') returns 6
• BUSINESSDAYS('2016-05-07','2016-05-13') returns 5
• BUSINESSDAYS('2016-05-13','2016-05-13') returns 1
• BUSINESSDAYS('2016-05-14','2016-05-14') returns 0

Number of weekdays (Monday - Friday) between two dates including both start and end.

• If StartDate is after EndDate, returns NULL.

IsLeapYear

Syntax: ISLEAPYEAR(Year)

Examples:

• ISLEAPYEAR(2016) returns TRUE
• ISLEAPYEAR(2010) returns FALSE
• ISLEAPYEAR(1900) returns FALSE
• ISLEAPYEAR(2000) returns TRUE

Is a year a leap year.

• Function takes a year not a date as an input, use IsLearYear(Year([Date]) for a Date input.