913730_en-US

liliconcom

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

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" );

 

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

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><DL><DD>G<HH24><::><MM><::><SS>
<P>Also, do you wish to format OR modify the underlaying data?&nbsp;</P></SS></MM></HH24></DD></DL></MM></YYYY>

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

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:

<BR />New Column( "Date format" );
:Date format <<	Formula(InFormat(:Original setting, "Format Pattern", "<YYYY><MM><DL><DD>G<HH24><::><MM><::><SS>", 0 )) << 
	Format( "yyyy-mm-ddThh:mm:ss", 19, 2 );
<P>&nbsp;</P>
<P><BR />For the first mode, it is quite likely that users will often need to define<STRONG> the same custom pattern again and again</STRONG>.<BR />-> There is a request on the wish list to provide users with a history.<BR /><BR /><A href="https://community.jmp.com/t5/JMP-Wish-List/Custom-Time-Date-Format-Pattern-history/m-p/609017#U609017" class="lia-mention-container-editor-message lia-img-icon-idea-thread lia-fa-icon lia-fa-idea lia-fa-thread lia-fa" target="_blank">Custom Time/Date Format Pattern: history</A>&nbsp;</P>
<P>#Efficionado&nbsp;</P></SS></MM></HH24></DD></DL></MM></YYYY>