User Tools

Site Tools


syntax:functions:date

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

ArgumentTypeDescription
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).


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.


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.)


See also

syntax/functions/date.txt · Last modified: 2021/08/30 01:59 by craigt