topic Changing char date column to numeric date in Discussions

Changing char date column to numeric date

cchueng — Sat, 27 Apr 2024 03:42:05 GMT

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!

Re: Changing char date column to numeric date

txnelson — Sat, 27 Apr 2024 04:24:23 GMT

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

cchueng — Sat, 27 Apr 2024 05:27:22 GMT

Thank you, this works!

Re: Changing char date column to numeric date

hogi — Sat, 27 Apr 2024 08:17:27 GMT

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 :
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

hogi — Sun, 28 Apr 2024 17:51:21 GMT

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 <ss> 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".

*)

(view in My Videos)