/wiki/spaces/DASSB100/pages/32569527069 Dates are one of the /wiki/spaces/DASSB100/pages/32569527069 primitive data types used in Datameer. However, it is often necessary (during data imports and exports as and exports as well as when working with certain functions) to convert a date represented as a /wiki/spaces/DASSB100/pages/32569527069 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.
...
Letter | Date or time component | Shown as | Example |
|---|---|---|---|
G | Era designator | Text | AD |
| C | Century of era | Number | 20 |
| YWeek year | 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 |
...
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 |
...
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
- 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.
...