Date and Time Functions
Media Center provides several functions for the conversion, formatting and generation of dates and times. Date and time for a Date-type field is stored internally as a single floating-point number, where the integer portion represents the number of days since the Epoch, and the decimal portion represents the fraction of a day in seconds. The Epoch is defined as December 30th, 1899 at 00:00:01. Certain fractional values and whole numbers are used to encode Time-only and Year-only values. For example, the internal Date value of "2" is considered as 1900, without a time when converted using the DateTime conversion format of FormatDate(), whereas adding a small fraction and using "2.001" instead produces a value of "1/1/1900 12:01 am". These details are only relevant if you are doing conversions.
The Windows locale setting will affect the interpretation and formatting of date and time information.
- Converts a human-readable date to the internal format required for use in date fields.
|Description|| convertdate(date_time string)
Converts a human-readable date_time string into the internal floating-point representation used by Media Center to store a date and time.
Returns the value 40974, which is the internal floating-point representation of the date string 3/6/2012. This value can used by any field of type Date, or in any function that requires as input a Date type value.
formatdate(convertdate(May 17 1970), dddd dd MMM yyyy)
Returns the value Sunday 17 May 1970, as the input does not need to be numeric, but can be in common human readable format, including "17 May 17", or "17-5-17", or "17 5 17", which all mean Wednesday 17 May 2017. Or "17 5", where the year is left out and defaults to the current year. Or "17 17", where the month is left out and default to the current month.
Converts the date string 12/2/1985 (note: December 2nd, not February 12th, though the function appears to follow system date formatting) into a Date type value, and then formats the result as a decades grouping, returning 1980's. This might be used for creating decade groupings.
- Functions as a switch or select case statement.
|Description|| DateInRange(Date, Range Start, Range End)
The DateInRange() function compares a date against a range of dates, returning "1" if the date is within the given range, and "0" if it is not.
Related: See also IsRange(…)
|Examples|| * All examples shown below use the UK date system of dd/mm/yyyy hh:mm
if(DateInRange([Date],17-03-2012 00:00,17-03-2012 12:00),The morning of March 17th 2012,Some other time)
- Formats a date value in a specified manner.
|Description|| formatdate(date value, format specifier, empty label)
The FormatDate() function provides custom formatting of date and time values through the use of a format specifier. Output will be formatted according to format specifier.
The date value is a Media Center internal floating-point date/time representation, stored in Date fields, and output from various functions such as Now() and ConvertDate(). To pass a field of type Date to FormatDate(), use the raw (unformatted) field specification, such as "[Date Imported,0]".
The format specifier provides a recipe for converting the internal value into a human-readable string. Supported are a variety of Windows style, strftime() style, and Media Center-specific formats specifiers. Construct the format specifier from any number or combination of those defined in the following table. Additionally, any non-format characters will be output without interpretation. This allows creating rich date/time output strings. To output a word that is a reserved format specifier, surround the word with double-quotes.
The empty label argument will be output if the date value is empty.
Argument date value is optional (defaults to [date,0]).
Argument empty label is optional (defaults to EMPTY).
Outputs the file's date formatted as Year-Month, such as 2012-April. The default date value of [Date,0] is used.
formatdate([last played,0], yyyy//MM//dd, Not Yet)
Returns the file's last played date as year/month/day without the time, ignoring the system locale setting. If a file has no last played value, the expression will output Not Yet instead.
formatdate([date modified,0], month %Y)
Returns the file's modification date/time in the form of a long month name and a four-digit year, such as December 2010.
formatdate([date imported,0], The "year" is year)
Outputs the The year is ####, where #### is the year the file was imported into the Library. Note that the word year must be surrounded in double-quotes to have it considered as literal text, and not the Year format specifier.
formatdate([date imported,0], month)&datatype=[month]
This examples is the same as the previous example, but includes a cast to the Month type &datatype=[month]. This cast can be used to cause chronological month-sorting, rather than month name alphabetic-sorting, in a panes or category view. Data-type coercion is discussed above.
- Retrieve and display the system date.
The Now() function returns a floating-point value representing the current system date and time. It is generally useful for performing date arithmatic in expressions that desire to figure out elapsed time. Any raw date field or value representing a date can be subtracted from Now() to realize an elapsed time delta.
When run on Aug 17, 2013 at 19:28:00, returns approximately 41503.811115995.
Returns the current date, without a time component, formatted according to the system's locale settings.
formatdate(math(now() - 3, dddd dd MMMM yyyy H:mm)
The date from three days ago is formatted as something like Wednesday 14 August 2013 19:35. This is accomplished by subtracting the value 3, which would be days, from Now(), and its output formatted by FormatDate().