Conversion of Date time from string from a table

I have my data in two separate column, one column for date and other have the time with millisecond.

I want to merge the two cloumn and convert them into datetime so that I can change the table to timetable.

P.S: my time format including milliseconds uses (:) symbol not (.) symbol. i.e. 23:59:0:0 not 23:59:0.0

I am able to read the data and able to append the columns

test = parquetread('P3007768_2020-11-01-23-59-00.parquet');

test.yyyy_mm_dd = char(append(test.yyyy_mm_dd,' ', test.h_m_s_ms));

% I have tried two options:

%% First option

d = datestr(datenum(test.yyyy_mm_dd, 'InputFormat','yyyy-mm-dd HH:MM:SS:FFF'));

Error using datenum (line 188)

DATENUM failed.

Caused by:

Error using matlab.internal.datetime.cnv2icudf (line 167)

Unrecognized millisecond format. Format: InputFormat.

%% Second option

d = datetime(test.yyyy_mm_dd,'InputFormat','yyyy-MM-dd HH:mm:ss.SSS');

Error using datetime (line 640)

Unable to convert the text to datetime using the format 'yyyy-MM-dd HH:mm:ss.SSS'.

Answer 1

[Cris LaPierre]

We are working from a screenshot, so this many not be 100% accurate, but I'd combine the two strings, and then used datetime to create a new table variable.

% Create sample table
yyyy_mm_dd = ["2020/11/1";"2020/11/1"];
h_m_s_ms = ["23:59:0:0";"23:59:0:10"];
test = table(yyyy_mm_dd,h_m_s_ms)

test = 2×2 table
    yyyy_mm_dd       h_m_s_ms  
    ___________    ____________

    "2020/11/1"    "23:59:0:0" 
    "2020/11/1"    "23:59:0:10"

% convert date and time to datetime
test.Date = datetime(test.yyyy_mm_dd + " " + test.h_m_s_ms,...
    "InputFormat","yyyy/MM/dd HH:mm:ss:SS", ...
    "Format","dd-MMM-yyyy HH:mm:ss.SS")

test = 2×3 table
    yyyy_mm_dd       h_m_s_ms               Date          
    ___________    ____________    _______________________

    "2020/11/1"    "23:59:0:0"     01-Nov-2020 23:59:00.00
    "2020/11/1"    "23:59:0:10"    01-Nov-2020 23:59:00.10

[Cris LaPierre]

Good catch by Stephen. Here's an updated approach.

% Create sample table
yyyy_mm_dd = ["2020/11/1";"2020/11/1"];
h_m_s_ms = ["23:59:0:0";"23:59:0:10"];
test = table(yyyy_mm_dd,h_m_s_ms);

% Start by extracting time numbers, and using duration and milliseconds to
% convert to duration
test.Date = str2double(extract(test.h_m_s_ms,digitsPattern));
test.Date = duration(test.Date(:,1:3))+milliseconds(test.Date(:,4))

test = 2×3 table
    yyyy_mm_dd       h_m_s_ms        Date  
    ___________    ____________    ________

    "2020/11/1"    "23:59:0:0"     23:59:00
    "2020/11/1"    "23:59:0:10"    23:59:00

% Convert date to datetime and add duration
test.Date = datetime(test.yyyy_mm_dd,"InputFormat","yyyy/MM/dd","Format",...
    "dd-MMM-yyyy HH:mm:ss.SSS") + test.Date

test = 2×3 table
    yyyy_mm_dd       h_m_s_ms                Date          
    ___________    ____________    ________________________

    "2020/11/1"    "23:59:0:0"     01-Nov-2020 23:59:00.000
    "2020/11/1"    "23:59:0:10"    01-Nov-2020 23:59:00.010

[Maitreyee Dey]

HI Cris,

Many thanks, I was struggeling to solve this since last month.

Really appreciate for your answer, it works very well. I have noted that instead of single quote i have to use double. Also I have added three SSS and that works well too.