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

[#1]

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?

[#2 · Accepted solution]

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);
);
//{"02.28.2021 23:00:00", "03.01.2021 0:00:00", "03.01.2021 20:00:00", "03.02.2021 0:00:00"} 

[#3]

I think @jthi  is correct, but test carefully around the edges. Some of the worst bugs I ever dealt with had to do with times.

• The worst one only happened before 10AM, but we didn't know that until someone came in early enough to catch it! (Leading blank at 9AM.)
• Or maybe it was the one that only happened during the last few seconds of each hour. Similar computation as suggested above, but done badly.

I really like how my auto insurance policy starts at 12:01AM to remove the ambiguity...

[#4]

I agree too, Craige_Hales!

but, I don't control this problem.

Fortunately, this problem only occurs at midnight.

Anyway~~Thank thanks a lot!

[#5]

 Thank you~jthi~~:)

I solved the problem by applying this.