This is an old revision of the document!
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. |
| format | Text | The layout of the date-time components in datetime describing the order and type of components. See the "Date-Time format components" tables below. |
Return value type: Number (date-time serial value).
Date-Time format components
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.
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)
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.)