750329_en-US

liliconcom

Changing char date column to numeric date

Hello,

Attached is a table with a column called StartDate. It is in char format and have excessive '0's at the end of each date. I wish to write a script to change the StartDate column to this format like : 04/12/2024 4:10:09 PM. Any help is appreciated.

 

Thanks!

 

Automation and ScriptingRe: Changing char date column to numeric date

Here is a little script that will create a new numeric date/time column converting your StartTime column values.

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "StartTime2",
	set each value(
		Informat( Word( 1, :StartTime, "+" ), "yyy/mm/ddThh:mm:ss" )
	),
	Format( "m/d/y h:m:s" )
);

 

 

Re: Changing char date column to numeric date

I wondered about the "T" ...

Actually,

dt = Current Data Table();
dt << New Column( "StartTime2",
	set each value(
		Informat( Word( 1, :StartTime, "+" ), "hello" )
	),
	Format( "m/d/y h:m:s" )
);

does the job as well

It's the power of Informat: For most cases there is no need to specify the pattern, Informat will detect it on it's own.
https://community.jmp.com/t5/Discussions/Informat-ParseDate-function-with-time/m-p/736131/highlight/true#M91680 

Just for some rare cases (like DD.MM.YYYY ) you have to invest some additional effort.
Either specify the pattern via "Format Pattern", your pattern ,
or via Date Format Writer from the  Data Table Tools Add-in :
https://community.jmp.com/t5/JMP-On-Air/The-Doctor-Cures-Your-Date-and-Time-Import-Problems/tac-p/735440/highlight/true#M351 

Re: Changing char date column to numeric date

Thank you, this works!

Re: Changing char date column to numeric date

maybe the easiest way:
open the column properties, and change the data type to numeric, then specify the pattern.

a) row 13 shows:
with the pattern Jmp automatically reads the seconds including the digits after the point.

 

b) The only thing that is not automatically detected is the timezone offset *)
→ if all values are the same, you can just add " +00:00" - to get it "ignored".

*) Date Format Pattern - additional functionality