Table of Contents
Date(datetime, format)
Category: Date and Time function
Description
This function converts datetime as text into a date-time value represented as a number (date-time serial value) according to format string.
Use cases
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.
Arguments
| Argument | Type | Description |
|---|---|---|
| datetime | Text | A text value representing a date and/or time value to be converted to a numeric date-time serial value. |
| format | Text | (Optional) The layout of the date-time components in datetime describing the order and type of the date-time components. If omitted, EasyMorph will attempt to detect the date format automatically. See the "Date-Time format components" tables below. |
Return value type: Number (date-time serial value).
Date-Time format string components
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. |
Remarks
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.
While it can be convenient, in some cases, to omit the format, it is recommended to specify the date format explicitly, when possible, to ensure deterministic behavior.
Examples
Dates:
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('2023-Nov-4') //(Omitted format) Returns 45234
Times:
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.)