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

C++ Function - MakeDate

Syntax: MAKEDATE(Year, Month, Day) Defaults: Month = 1, Day = 1

Examples:

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

Creates a date from numerical inputs of Year, Month and Day. Month and Day are optional inputs defaulting to 1.

• As of version 1.3, this checks the validity of the inputs and will warn if invalid. Please note that 0, 0, 0 is a valid input (i.e. no warning message) returning NULL.

MakeTime

C++ Function - MakeTime

Added to Time category

Syntax: MAKETIME(Hour, Minute, Second) Defaults: Hour = 0, Minute = 0, Second = 0

Examples:

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

Creates a time from numerical inputs of Hour, Minute and Second. All arguments are optional, defaulting to 0.

• As of version 1.3, this checks the validity of the inputs and will warn if invalid.

MakeDateTime

C++ Function - MakeDateTime

Added to DateTime category

Syntax: MAKEDATETIME(Year, Month, Day, Hour, Minute, Second) Defaults: Month = 1, Day = 1, Hour = 0, Minute = 0, Second = 0

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. All arguments except Year ae optional.

• As of version 1.3, this checks the validity of the inputs and will warn if invalid.

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.

ToTime

Syntax: TOTIME(DateTime)

Examples:

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

Gets the time of day from the input.

• Only sanity checking is on the length of the field past. If not 10 (a Date) or 19 (a DateTime) or 8 (a Time), 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)

DatePart

Syntax: DATEPART(Interval, DateTime)

Examples:

• DATEPART('d', '2010-12-21') returns 21
• DATEPART('mm', '2010-12-21') returns 12
• DATEPART('%y', '2010-12-21') returns 2010
• DATEPART('NOIDEA', '2010-12-21') returns NULL
• DATEPART(NULL(), '2010-12-21') returns NULL
• DATEPART('d', NULL()) returns NULL

Returns a specified part of a datetime. This is returned a number.

Interval Specifiers

Interval	Meaning
century, cc, c, %c	Century
year, yyyy, yy, %y	Year
quarter, qq, q	Quarter
month, mm, m, %m (Case sensitive)	Month
dayofyear, dy, y, %j	Day of the year
day, dd, d, %d, %e	Day
weekday, dw, w, %w	Weekday (0 Sunday - 6 Saturday)
%u	Weekday (1 Monday - 7 Sunday)
week, ww, wk, %u	Week number (Starting on Sunday before or equal to 1st January)
hour, hh, h, %h, %k	Hour in 24 hour format (0 - 23)
%i, %l	Hour in 12 hour format (1 - 12)
minute, mi, n, %M (Case sensitive)	Minute
second, ss, s, %s	Second

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 a number.

Century

Syntax: CENTURY(DateTime)

Examples:

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

Returns the century as a 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).

Hour

Added to Time category

Syntax: HOUR(DateTime)

Examples:

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

Returns the hour as a number (0 - 23).

Minute

Added to Time category

Syntax: MINUTE(DateTime)

Examples:

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

Returns the minute as a number (0 - 59).

Second

Added to Time category

Syntax: SECOND(DateTime)

Examples:

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

Returns the second as a number (0 - 59).

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.