Category: Date and Time function
This function converts datetime as text into a date-time value represented as a number (date-time serial value) according to format string.
Date is used to convert a text-based date-time value into its corresponding date-time serial value so it may be used in date-time-related calculations, e.g. the differences between two dates, day-of-the-week determinations, etc.
|datetime||Text||A text value representing a date and/or time value to be converted to a numeric date-time serial value.|
|format||Text||The layout of the date-time components in datetime describing the order and type of the date-time components. See the "Date-Time format components" tables below.|
Return value type: Number (date-time serial value).
A format string defines a formatting template for dates and times. Its components can be put together in any combination. If a character doesn't belong to a format component then it's inserted verbatim.
The specifiers below are case-sensitive .
|Date format specifier||Description|
|d||The day of the month, from 1 through 31.|
|dd||The day of the month, from 01 through 31.|
|ddd||The abbreviated name of the day of the week.|
|M||The month, from 1 through 12.|
|MM||The month, from 01 through 12.|
|MMM||The abbreviated name of the month.|
|MMMM||The full name of the month.|
|y||The year, from 0 to 99.|
|yy||The year, from 00 to 99.|
|yyyy||The year as a four-digit number.|
|Time format specifier||Description|
|h||The hour, using a 12-hour clock from 1 to 12.|
|hh||The hour, using a 12-hour clock from 01 to 12.|
|H||The hour, using a 24-hour clock from 0 to 23.|
|HH||The hour, using a 24-hour clock from 00 to 23.|
|m||The minute, from 0 through 59.|
|mm||The minute, from 00 through 59.|
|s||The second, from 0 through 59.|
|ss||The second, from 00 through 59.|
|t||The first character of the AM/PM designator.|
|tt||The AM/PM designator.|
This function returns the date-time serial value of the datetime input but, depending on the format of the column, may display it as a readable date or a date-serial value. For example, January 1st, 2014 is represented as "41640" numerically. EasyMoprh displays this value as the readable date "01-Jan-2014" if a date-time format for the column has been selected. Under the hood, the actual value is "41640".
To see the date-time serial values in a column of dates, double-click the column's heading and select the "Metadata" tab in the "Filter/Profile" window. In the "Number format" drop-down, select "No format" to have the date-time serial numbers display in the column instead of the formatted dates. Switch back to one of the date-time formats to view the formatted date-times.
If a time value is used for the datetime argument, the return value will use today's date for the date component.
date('20140101', 'yyyyMMdd') //Returns 41640 (Displays as 01-01-2014.)
date('14-01-01', 'yy-MM-dd') //Returns 41640 (Displays as 01-01-2014.)
date('30/12/2000', 'dd/MM/yyyy') //Returns 44556 (Displays as 30-12-2000.)
date('20140101','yy-MM-dd') //Returns #Can't convert to date (Invalid format)
date('20140101 0300AM', 'yyyyMMdd hhmmtt') //Returns 41640.125 (Displays as 2014-01-01 03:00:00.)
date('14-01-01 120030', 'yy-MM-dd HHmmss') //Returns 41640.5003472222 (Displays as 2014-01-01 12:00:30.)
date('0600PM', 'hhmmtt') //Returns 44437.75 (Displays as 2021-08-29 18:00:00; uses current date.)