topic Date Time Formatting in Discussions

Date Time Formatting

dkraeuter_sunne — Tue, 08 Oct 2024 15:09:39 GMT

I have a column with a few different date formats and also some blank dates. How do I revise the column to show one date column type for the different date types? Below is a screenshot of some of the values.

Re: Date Time Formatting

txnelson — Tue, 08 Oct 2024 16:14:42 GMT

Here is one way to handle it

Names Default To Here( 1 );

dt = New Table( "Example",
	Add Rows( 2 ),
	New Column( "datetime",
		Character,
		"Nominal",
		Set Values( {"2023-05-10 17:47:36.005390800+00:00", "6/6/2024 9:06"} ),
		Set Display Width( 230 )
	)
);

New Column( "new datetime", Numeric, "Continuous", Format( "m/d/y h:m:s", 23, 0 ) );

For Each Row(
	If( contains(:datetime, "+"),
		theDateTime = word(1,:datetime,"+");
		substitute into(theDateTime," ","T");
		:new datetime = informat(theDateTime,"yyyy-mm-ddThh:mm:ss");
		,
		contains(:datetime,"/"),
		:new datetime = informat(:datetime,"m/d/y h:m")
	)
);

Re: Date Time Formatting

jthi — Tue, 08 Oct 2024 16:51:55 GMT

I would go with something similar as Jim. Use If-statement to check which format you are currently handling and then use InFormat() to turn it into a numeric value

Names Default To Here(1);

dt = New Table("Example",
	Add Rows(2),
	New Column("datetime",
		Character,
		"Nominal",
		Set Values({"2023-05-10 17:47:36.005390800+00:00", "6/6/2024 9:06"})
	)
);

// If you wish to keep milliseconds some extra stuff is needed
dt << new column("date", numeric, continuous, Format("Locale Date Time h:m:s", 23, 0),
	Formula(
		If(Contains(:datetime, "+"),
			pattern = "<YYYY>-<MM>-<DD> <hh24>:<mm>:<ss>";
		,
			pattern = "<DD>/<MM>/<YYYY> <hh24>:<mm>";
		);
		In Format(
			Word(1, :datetime, "."),
			"Format Pattern",
			pattern
		);
	)
);