804241_zh-TW

liliconcom

日期時間格式

我有一列包含幾種不同的日期格式和一些空白日期。如何修改該列以顯示不同日期類型的一種日期列類型？下面是一些值的螢幕截圖。

自動化和腳本編寫資料混合與清理回覆：日期時間格式

這是一種處理方法

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

回覆：日期時間格式

我會選擇類似吉姆的東西。使用 If 語句檢查目前正在處理的格式，然後使用InFormat()將其轉換為數值

/<MM>/<YYYY> <HH24>:<MM>";
		);
		In Format(
			Word(1, :datetime, "."),
			"Format Pattern",
			pattern
		);
	)
);
</MM></HH24></YYYY></MM>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>-<DL><DD> <HH24>:<MM>:<SS>";
		,
			pattern = "</SS></MM></HH24></DD></DL></MM></YYYY>