Form - Cleaning Date Time data - Revision history

Philip Lacey at 19:48, 12 October 2017

2017-10-12T19:48:24Z

Philip Lacey: /* Outputs */

2017-10-12T19:34:05Z

‎Outputs

Philip Lacey: /* Outputs */

2017-10-12T19:33:18Z

‎Outputs

Philip Lacey: /* Outputs */

2017-10-12T18:53:41Z

‎Outputs

Philip Lacey at 18:53, 12 October 2017

2017-10-12T18:53:19Z

Philip Lacey: Created page with "= Overview = Date and time formats vary wildly. American and European dates can become easily confused when used in Excel or other tools which think they're being used on a..."

2017-10-12T16:22:59Z

Created page with "= Overview = Date and time formats vary wildly. American and European dates can become easily confused when used in Excel or other tools which think they're being used on a..."

New page

= Overview =

Date and time formats vary wildly. American and European dates can become easily confused when used in Excel or other tools which think they're being used on a different continent.

Throughout bxp date time fields use Universal Date Time format. This means YYYY-MM-DD HH:mm:SS
* Y : Year
* M : Month
* D : Day
* H : Hour
* m : minute
* S : Second

Universal Date Time format has a number of distinct advantages.
* It's unambiguous.
* When ordered by a database and the contents are a string (as opposed to a date time field), they will order correctly

To explain this last point. You have two dates 29/01/2017, which is the 29th of January 2017. You also have the 01/09/2017, the 1st of September 2017. In an ordered list you would expect this to be
* 29/01/2017
* 01/09/2017

Unfortunately by default when this data is text, 0 is < 2 so the list actually outputs as
* 01/09/2017
* 29/01/2017

With UDT, even if the data is text and not a date time field, it will always sort correctly.
* 2017-01-29
* 2017-09-01

= Cleaning data =

When you upload data into bxp from an Excel spreadsheet or even injected from an external website or app, the data can come in, in European or American format. To save time, bxp has a mass date time correction function.

''Main Menu > Form - Management > Form - Data Cleaning > Mass Update - Convert data to UDTF''

@@ Line 42: / Line 42: @@
 ''Main Menu > Form - Management > Form - Data Cleaning > Mass Update - Convert data to UDTF''
 = Outputs =
 This table of sample data is provided as an example of how the system will handle your data.  European format with no dates.

@@ Line 50: / Line 50: @@
 {| class="wikitable"
-! Sample Data
+! Sample Raw Data
-! Converts to
+! European Conversion
-|-
+! American Conversion
 |-
 | 01/01/17 || 2017-01-01 || 2017-01-01

@@ Line 53: / Line 53: @@
 ! Converts to
 |-
-| Basic Formats || Nothing
+| Basic Formats || European || American
 |-
-| 01/01/17 || 2017-01-01
+| 01/01/17 || 2017-01-01 || 2017-01-01
 |-
-| 01/01/2017 || 2017-01-01
+| 01/01/2017 || 2017-01-01 || 2017-01-01
 |-
-| 1/1/17 ||  2017-01-01
+| 1/1/17 ||  2017-01-01 || 2017-01-01
 |-
-| 1/1/2017 || 2017-01-01
+| 1/1/2017 || 2017-01-01 || 2017-01-01
 |-
-| 1/Jan/17 || 2017-01-01
+| 1/Jan/17 || 2017-01-01 || 2017-01-01
 |-
-| 1/Jan/2017 || 2017-01-01
+| 1/Jan/2017 || 2017-01-01 || 2017-01-01
 |-
-| 1 Jan 17 || 2017-01-01
+| 1 Jan 17 || 2017-01-01 || 2017-01-01
 |-
-| 1 Jan 2017 || 2017-01-01
+| 1 Jan 2017 || 2017-01-01 || 2017-01-01
 |-
-| 1 January 17 || 2017-01-01
+| 1 January 17 || 2017-01-01 || 2017-01-01
 |-
-| 1 January 2017 || 2017-01-01
+| 1 January 2017 || 2017-01-01 || 2017-01-01
 |-
-| 1st January 17 ||  2017-01-01
+| 1st January 17 ||  2017-01-01 || 2017-01-01
 |-
-| 1st January 2017 || 2017-01-01
+| 1st January 2017 || 2017-01-01 || 2017-01-01
 |-
-| 2017-1-1 || 2017-01-01
+| 2017-1-1 || 2017-01-01 || 2017-01-01
 |-
-| 2017-01-01 || 2017-01-01
+| 2017-01-01 || 2017-01-01 || 2017-01-01
 |-
-| Ambiguous || Nothing
+| Ambiguous || ||
 |-
-| 10/9/2017 || 2017-09-10
+| 10/9/2017 || 2017-09-10  || 2017-10-09
 |-
-| 9/10/2017 || 2017-10-09
+| 9/10/2017 || 2017-10-09 || 2017-09-10
 |-
-| Wrong for testing ||
+| Wrong for testing || ||
 |-
-| 13/1/2017 || 2017-01-13
+| 13/1/2017 || 2017-01-13 || 2017-01-13
 |-
-| 1/13/2017 || 2017-01-13
+| 1/13/2017 || 2017-01-13 || 2017-01-13
 |-
-| 02/31/2017 || Nothing
+| 02/31/2017 || Nothing || Nothing
 |-
-| 31/02/2017 || Nothing
+| 31/02/2017 || Nothing || Nothing
 |-
-| 2017-02-31 || Nothing
+| 2017-02-31 || Nothing || Nothing
 |-
 |}

@@ Line 101: / Line 101: @@
 | 2017-02-31 || Nothing
 |-
-||}
+|}

← Older revision		Revision as of 18:53, 12 October 2017
Line 41:		Line 41:

	''Main Menu > Form - Management > Form - Data Cleaning > Mass Update - Convert data to UDTF''		''Main Menu > Form - Management > Form - Data Cleaning > Mass Update - Convert data to UDTF''
		+
		+
		+
		+
		+	= Outputs =
		+
		+	This table of sample data is provided as an example of how the system will handle your data. European format with no dates.
		+
		+	{\| class="wikitable"
		+	! Sample Data
		+	! Converts to
		+	\|-
		+	\| Basic Formats \|\| Nothing
		+	\|-
		+	\| 01/01/17 \|\| 2017-01-01
		+	\|-
		+	\| 01/01/2017 \|\| 2017-01-01
		+	\|-
		+	\| 1/1/17 \|\| 2017-01-01
		+	\|-
		+	\| 1/1/2017 \|\| 2017-01-01
		+	\|-
		+	\| 1/Jan/17 \|\| 2017-01-01
		+	\|-
		+	\| 1/Jan/2017 \|\| 2017-01-01
		+	\|-
		+	\| 1 Jan 17 \|\| 2017-01-01
		+	\|-
		+	\| 1 Jan 2017 \|\| 2017-01-01
		+	\|-
		+	\| 1 January 17 \|\| 2017-01-01
		+	\|-
		+	\| 1 January 2017 \|\| 2017-01-01
		+	\|-
		+	\| 1st January 17 \|\| 2017-01-01
		+	\|-
		+	\| 1st January 2017 \|\| 2017-01-01
		+	\|-
		+	\| 2017-1-1 \|\| 2017-01-01
		+	\|-
		+	\| 2017-01-01 \|\| 2017-01-01
		+	\|-
		+	\| Ambiguous \|\| Nothing
		+	\|-
		+	\| 10/9/2017 \|\| 2017-09-10
		+	\|-
		+	\| 9/10/2017 \|\| 2017-10-09
		+	\|-
		+	\| Wrong for testing \|\|
		+	\|-
		+	\| 13/1/2017 \|\| 2017-01-13
		+	\|-
		+	\| 1/13/2017 \|\| 2017-01-13
		+	\|-
		+	\| 02/31/2017 \|\| Nothing
		+	\|-
		+	\| 31/02/2017 \|\| Nothing
		+	\|-
		+	\| 2017-02-31 \|\| Nothing
		+	\|-
		+	\|\|}