topic How to Convert a Column to Date-Time Format (YYYY-MM-DD HH:MM:SS)? in Discussions

How to Convert a Column to Date-Time Format (YYYY-MM-DD HH:MM:SS)?

BabyDoragon — Mon, 17 Nov 2025 07:41:14 GMT

How to correctly convert a column into date and time elements? For example, in the following JSL, the original data is "20230507G10:11:59". How can I convert the data type into a numeric date format like "YYYY-MM-DD H:M:S", such as "2023-05-07 10:11:59"?

dt = New Table( "Untitled");
New Column( "Original setting" );
:Original setting << Data Type( Character );
:Original setting << Set Values( {"20230507G10:11:59", "20230912G11:14:50", "20241211G15:21:14"} );
New Column( "Date format" );
:Date format<< Data Type( Character );
:Date format << Set Values( {"20230507G10:11:59", "20230912G11:14:50", "20241211G15:21:14"});

Column( dt, "Date format" ) << Format( "yyyy-mm-dd" );

Re: How to Convert a Column to Date-Time Format (YYYY-MM-DD HH:MM:SS)?

jthi — Mon, 17 Nov 2025 09:14:23 GMT

Do it once manually using custom format and format pattern and then copy the script from log. Format pattern is most likely something like this

<YYYY><MM><DD>G<hh24><::><mm><::><ss>

Also, do you wish to format OR modify the underlaying data?

Re: How to Convert a Column to Date-Time Format (YYYY-MM-DD HH:MM:SS)?

hogi — Tue, 18 Nov 2025 06:21:56 GMT

Option 1: converting the Data Type of the second column to numeric and AT THE SAME TIME adjust the display/ and Input Format Pattern:

Option 2: define the conversion via a column formula:


New Column( "Date format" );
:Date format <<	Formula(InFormat(:Original setting, "Format Pattern", "<YYYY><MM><DD>G<hh24><::><mm><::><ss>", 0 )) << 
	Format( "yyyy-mm-ddThh:mm:ss", 19, 2 );

For the first mode, it is quite likely that users will often need to define the same custom pattern again and again.
-> There is a request on the wish list to provide users with a history.

#Efficionado