Problem with datetime in German

From a German language spreadsheet I have the following DateString: '21.Okt.2020'

where Okt is short for October.

How to read this as a datetime? According to the documentation the following should work, but unfortunately doesn't:

datetime('21.Okt.2020','Locale','de_DE','InputFormat','dd.MMM.yyyy')

Any advice would be greatly appreciated.

Regards

Answer 1

[Stephen23]

There does seem to be a problem with DATETIME handling the period characters (below).

Workaround: replace/remove the period characters, as per my first two examples.

datetime('21Okt2020','Locale','de_DE','InputFormat','ddMMMyyyy')

ans = datetime
   21-Oct-2020

datetime('21-Okt-2020','Locale','de_DE','InputFormat','dd-MMM-yyyy')

ans = datetime
   21-Oct-2020

datetime('21.Okt.2020','Locale','de_DE','InputFormat','dd.MMM.yyyy')

Error using datetime (line 651)
Unable to convert '21.Okt.2020' to datetime using the format 'dd.MMM.yyyy' and locale 'de_DE'.

[Stephen23]

In case anyone suggests to put the period character inside single quotes:

datetime('21.Okt.2020','Locale','de_DE','InputFormat','dd''.''MMM''.''yyyy')

Error using datetime (line 651)
Unable to convert '21.Okt.2020' to datetime using the format 'dd'.'MMM'.'yyyy' and locale 'de_DE'.

[Joshua]

Thanks for the answer!

Answer 2

[Eric Sofen]

We’re aware of the issues with parsing month abbreviations in some locales, and we're looking at ways to improve this. In this case, the "." following Okt is treated as part of the month abbreviation by the internationalization library we use. For now, the datetime format that works to parse the timestamp is:

datetime('21.Okt.2020','Locale','de_DE','InputFormat','dd.MMMyyyy')

(Notice no period after MMM.)

Answer 3

[Walter Roberson]

Observe:

datetime('21.Oct.2020', 'InputFormat', 'dd.MMM.yyyy', 'Format', 'dd.MMM.yyyy')

ans = datetime
   21.Oct.2020

So using dots is possible for English.

datetime('21-Okt-2020', 'Locale', 'de_DE', 'InputFormat', 'dd-MMM-yyyy', 'Format', 'dd.MMM.yyyy')

ans = datetime
   21.Oct.2020

And using dashes is okay for German

datetime('21.Okt-2020', 'Locale', 'de_DE', 'InputFormat', 'dd.MMM-yyyy', 'Format', 'dd.MMM.yyyy')

ans = datetime
   21.Oct.2020

and dot between the day and the month is ok in German

datetime('21.Okt.2020', 'Locale', 'de_DE', 'InputFormat', 'dd.MMM.yyyy', 'Format', 'dd.MMM.yyyy')

Error using datetime (line 651)
Unable to convert '21.Okt.2020' to datetime using the format 'dd.MMM.yyyy' and locale 'de_DE'.

But dot between the year and the month is not okay in German.

I do not know why this is... but see the following

[Walter Roberson]

datetime('21.Okt..2020', 'Locale', 'de_DE', 'InputFormat', 'dd.MMM.yyyy', 'Format', 'dd.MMM.yyyy')

ans = datetime
   21.Oct.2020

I do not know quite what to make of this.

[Joshua]

@Walter Roberson thanks for your effort, does indeed seem like an issue for Mathworks. In my case I have a bunch of different dates with often different formating. In the end I ended up parsing and replacing the '.' with '-' as both you and @Stephen suggested.