- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)
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"}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)
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"}
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)
I agree too, Craige_Hales!
but, I don't control this problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to convert string....('20210228240000' ----> 2021-03-01T00:00:00)
Thank you~jthi~~:)
I solved the problem by applying this.