Dates are one of the primitive data types used in Datameer. However, it is often necessary (during data imports and exports as well as when working with certain functions) to convert a date represented as a string to a form that is recognized as a date by Datameer. This is done by parsing the pattern so that it can be correctly interpreted. Use the table below to define the parse pattern.

Parse Patterns

Letter	Date or time component	Shown as	Example
G	Era designator	Text	AD
C	Century of era	Number	20
Y	Year of era	Year	1996
w	Week in year	Number	27
e	Day in week	Number	2
E, EE, or EEE - EEEE	Day of the week	Text	Tue - Tuesday
y	Year	Year	1996; 96
D	Day in year	Number	189
MM - MMM; MMMM	Month of year	Month	01 - Jan; January
d	Day in month	Number	10
a	Am/pm marker	Text	PM
K	Hour in am/pm (0-11)	Number	0
h	Hour in am/pm (1-12)	Number	12
H	Hour in day (0-23)	Number	0
k	Hour in day (1-24)	Number	24
m	Minute in hour	Number	30
s	Second in minute	Number	55
S	Millisecond	Number	978
x	Week's year	Number	14

Examples

From string to date

In a workbook, a column that is of a string type can be changed to a date by using the function ASDATE.

Date	Parse pattern	Displayed date in Datameer
12/27/08	MM/dd/yy	Dec 27, 2008 12:00:00 AM
15 Oct 2002	dd MMM yyyy	Oct 15, 2002 12:00:00 AM
25.09.05 12:23:05	dd.MM.yy HH:mm:ss	Sep 25, 2005 12:23:05 PM
Monday, January 3, 2011	E, MMM d, yyyy	Jan 3, 2001 12:00:00 AM
14.01.2003 T 15:33:15	dd.MM.yyyy 'T' HH:mm:ss	Jan 14, 2003 03:55:15 PM
01.01.2001 CET 01:01:01	dd.MM.yyyy ZZZ HH:mm:ss	Jan 1, 2001 01:01:01 AM
2009-06-30T18:30:00.001	yyyy-MM-dd'T'HH:mm:ss.SSS	Jun 30, 2009 06:30:00 PM

For time zones

Parse patterns for time zones going from string to date.

Letter	Date or Time Component	Shown as	Example
ZZZ	Time zone	General Time Zone	PST; Pacific Standard Time; America/Los_Angeles
Z	Time zone	General Time Zone	-0800 - -08:00

The time displayed is converted to the local time set in the system preferences where Datameer X is running.

From date to string

In a workbook, a column that is of a date type can be changed to a string by using the function FORMATDATE

Date	Pattern	Displayed date as string
Dec 27, 2008 12:00:00 AM	MM/dd/yy	12/27/08
Oct 15, 2002 12:00:00 AM	dd MMM yyyy, EEEE	15 Oct 2002, Tuesday
Sep 25, 2005 12:23:05 PM	dd.MM.yy HH:mm:ss	25.09.05 12:23:05
Jan 3, 2011 12:00:00 AM	E, MMM d, yyyy	Mon, Jan 3, 2011
Jan 14, 2003 03:55:15 AM	dd.MM.yyyy 'T' HH:mm:ss a	14.01.2003 T 3:55:15 AM
Jan 14, 2003 03:33:00 PM	dd.MM.yyyy HH:mm Z	14.01.2003 15:33 +0100
Jan 14, 2003 03:33:00 PM	dd.MM.yyyy HH:mm ZZ	14.01.2003 15:33 +01:00
Jan 14, 2003 03:33:00 PM	dd.MM.yyyy HH:mm ZZZ	14.01.2003 15:33 Europe/Berlin
Jan 14, 2003 03:33:00 PM	dd.MM.yyyy HH:mm z	14.01.2003 15:33 CET

For time zones

Parse patterns for time zones going from date to string.

Letter	Date or Time Component	Shown as	Example
z	Time zone	General Time Zone	CET
Z	Time zone	General Time Zone	+0100
ZZ	Time zone	General Time Zone	+01:00
ZZZ	Time zone	General Time Zone	Europe/Berlin

The time zone displayed is the local timezone set in the system preferences where Datameer X is running.

Parse Pattern Details

Keep in mind the following information about parse patterns in Datameer X

In Datameer X a date always includes a time. If the date you are using does not include a time, then 00:00:00 is added by default.
If the date and time string includes characters which are not defined above, use single quotes, e.g. '<characters>', around these characters when parsing.
If a pattern can result in a single digit, such for the following values:
- Week in year
- Day in year
- Day in week
- Day in month
- Hour in am/pm
- Hour in day
- Minute in hour
- Second in minute
- Millisecond
Then more significant digits or leading zeros can be added before the number if applicable by doubling or tripling the letter marker. For example, if it is January 3 and you looking for the day in the year, D returns 3, DD returns 03, and DDD returns 003. However, using D, DD, or DDD on the 300th day of the year all return 300.
When parsing from a string to a date, Monday (or Mon) and July (or Jul) can be represented by EEE and MMM, respectively. However when parsing from a date to a string, EEE returns "Mon" whereas EEEE returns "Monday". MMM returns "Jul" and MMMM returns "July".
When parsing the abbreviated year pattern ("y" or "yy"), the SimpleDateFormat must be interpreted relative to a century. This is accomplished by assuming the dates to be within 80 years before and 20 years after the point in time when a specific SimpleDateFormat instance was created. For example, using the pattern "MM/dd/yy" and a SimpleDateFormat instance created on Jan 1, 1997, the string "01/11/12" is interpreted as Jan 11, 2012 while the string "05/04/64" is interpreted as May 4, 1964.
Note that Datameer X uses Joda Time for Dates.
Parquet files using the INT96 format are interpreted as time stamps. Datameer X accepts those columns, but cuts off the nanoseconds.

Browser not supported