How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)

stryper24

The data should be saved as '20210301000000' but It is stored as '20210228240000' in the SAP.
I try to use 'substr function', but Problems always occur at the end of the month.

Finally, as '2021-02-29' is returned, an error occurs in JMP if the date does not exist.

Excel recognizes the date automatically...[ = date(2021, 2, 29) ---> 2021-03-01 ]

I think...First, cut '20210228' from the record, and converting the date, and adding 1 day (86400 seconds) ????

Is there an easier function or method?

질문.xlsx

jthi

I think you should be fine with following process:

1. Extract date
2. Convert date to seconds
3. Extract hours, minutes, second and convert to seconds
4. Combine date in seconds and time in seconds
5. Convert seconds to desired format

One example:

 

Names Default To Here(1);

str_list = {
	"20210228230000",
	"20210228240000", 
	"20210301200000",
	"20210301240000"
};

dates = Transform Each({str}, str_list,
	y = Num(Left(str, 4));
	m = Num(Substr(str, 5, 2));
	d = Num(Substr(str, 7, 2));

	hh = Num(Substr(str, 9, 2));
	mm = Num(Substr(str, 11, 2));
	ss = Num(Substr(str, 13, 2));

	date_time = Date MDY(m, d, y) + In Hours(hh) + In Minutes(mm) + ss;

	 MDYHMS(date_time);
);<BR />//{"02.28.2021 23:00:00", "03.01.2021 0:00:00", "03.01.2021 20:00:00", "03.02.2021 0:00:00"}&nbsp;