Issues with Converting dates into the right datetime format

Hi,

I am bringing in some data from Analysis Services (SSAS), and I am having trouble converting the dates into the right datetime format. From the query we get the cell array of dates to be:

We want to get this into datetime format, so we use a bespoke function to make this work:

function OutputArray = DateConversion(DateArray)
% This function converts the dates that come out of Analysis Services into
% datetime formats so that they can be used by Matlab. This was created due
% to Matlab changing dates at dd/mm/yyyy 00:00 to dd/mm/yyyy.
% 
% Version 1: Used a loop - Was 35 times slower
%
% Version 2: Uses code from Calvin, which does it all through logical
% indexing, and doesn't require loops.
%
% Written by James McBrearty - 2019 - Energia

% Initialising the format of the Times Array
Times = datetime(DateArray,'format','dd/MM/yyyy HH:mm:00');

% Obtaining the Times that come out as NaT, and formats them correctly.
Times(isnat(Times),1) = datetime(DateArray(isnat(Times),1),...
    'Format','dd/MM/uuuu 00:00:00');

% The Final Output
OutputArray = Times;

end

Where in this case the DateArray that will be entered in will be FCStruct.StartDateTime, and this is also the output. This is run as follows:

% Converting the dates returned by the query into the right format
FCStruct.StartDateTime = DateConversion(FCStruct.StartDateTime);

This works, however, we get warnings:

Warning: Successfully read the date/time text using the format 'MM/dd/uuuu HH:mm:ss', but their format is ambiguous and could also be 'dd/MM/uuuu HH:mm:ss'. Specify a format

character vector to avoid ambiguity.

> In guessFormat (line 66)

In datetime (line 610)

In DateConversion (line 17)

In CustDemandComparison (line 83)

What is the issue with the datestring/datetime conversion code? I did have a loop that done this, however, it was ~35 times slower than this method. It seems that I am using the format as expected however, there may be a more efficient way of doing this by using cellfunctions.

Thanks,

James

Answer 1

[Guillaume]

'Format' specifies the display format of datetime. It does not specify the format of the input to datetime. You specify this format with 'InputFormat'. You get the warning because you haven't specified the 'InputFormat' and hence datetime attemps to autodetect that format and it's ambiguous.

Times = datetime(DateArray, 'InputFormat', 'dd/MM/yyyy HH:mm:ss', 'Format', 'preserveinput');

'Format', 'preserveinput' use the input format for display. Of course, you can use a different display format.

[James McBrearty]

Thanks Guilllaume.

I always seem to have issues with datetime conversions, but I think I got this sorted now. The main issue was the inconsistent date format in the original cell array. I've managed to make this use a cellfunction now, and it is much quicker again, going from 0.01 seconds for a 384*1 cell array, to 0.006 seconds using cellfunctions.

Again, many thanks!