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.
Converts the date string 12/2/1985 (note: December 2nd, not February 12th) 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.
- 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().