Skip to content

Date and Time Functions (DateUtils.xml)

Jump to bottom Edit New page
James Dunkerley edited this page Mar 27, 2019 · 17 revisions

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.
Add a custom footer

Home

Developing Functions

Clone this wiki locally