Issues with Converting dates into the right datetime format
10 views (last 30 days)
Show older comments
James McBrearty
on 22 Oct 2019
Commented: James McBrearty
on 22 Oct 2019
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:
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/243938/image.png)
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
0 Comments
Accepted Answer
Guillaume
on 22 Oct 2019
Edited: Guillaume
on 22 Oct 2019
'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.
More Answers (0)
See Also
Categories
Find more on Calendar in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!