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

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

stryper24 — Fri, 09 Jun 2023 18:08:28 GMT

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?

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

jthi — Mon, 10 Jan 2022 09:27:30 GMT

I think you should be fine with following process:

Extract date
Convert date to seconds
Extract hours, minutes, second and convert to seconds
Combine date in seconds and time in seconds
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"}

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

Craige_Hales — Mon, 10 Jan 2022 13:30:16 GMT

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

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

stryper24 — Tue, 11 Jan 2022 00:09:08 GMT

Thank you~jthi~~:)

I solved the problem by applying this.

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

stryper24 — Tue, 11 Jan 2022 00:18:03 GMT

I agree too, Craige_Hales!

but, I don't control this problem.

Fortunately, this problem only occurs at midnight.

Anyway~~Thank thanks a lot!