How can I change an entire column of dates into datetime format?

15 views (last 30 days)
Hello,
I am trying to convert an entire column of dates into datetime format.
the first cell of the column reads as follows:
'1940/01/11 18:00:00+00'
I have tried the following code;
datetime(Table.ColumnName, 'InputFormat', 'dd-MMM-uuuu HH:mm:ss.SSS')
but keep getting an error and that it is Unable to convert the text to datetime using the
format 'dd-MMM-uuuu HH:mm:ss.SSS'.
Can someone let me know what I am doing wrong and provide a solution?
Thank you!!!
  1 Comment
Stephen23
Stephen23 on 29 Sep 2023
Edited: Stephen23 on 29 Sep 2023

“Can someone let me know what I am doing wrong and provide a solution?“

Just to make it clear: the “+00“ bit at the end most likely indicates the timezone:

https://en.wikipedia.org/wiki/Time_zone

And not fractional seconds like you specified. The solution is to specify the timezone in the input format (i.e. Z or X or as a literal).

Sign in to comment.

Answers (2)

Star Strider
Star Strider on 28 Sep 2023
Edited: Star Strider on 28 Sep 2023
It appears to be UCT (or GMT in 1940).
Perhaps this (the 'InputFormat' descriptor must match the format of the string you want to convert) —
Table.ColumnName = '1940/01/11 18:00:00+00';
DT = datetime(Table.ColumnName, 'InputFormat', 'uuuu/MM/dd HH:mm:ss+00', 'Timezone','UCT')
Warning: 'UCT' specifies a time zone with a fixed offset from UTC, +00:00. This zone does not follow daylight saving time, and so may give unexpected results. See the datetime.TimeZone property for details about specifying time zones.
DT = datetime
11-Jan-1940 18:00:00
EDIT — Be sure that this is 11 Jan and not 01 Nov. The day and month fields are ambiguous.
.

Piiotr Botew
Piiotr Botew on 28 Sep 2023
Edited: Piiotr Botew on 28 Sep 2023
You can use this format:
infmt = 'yyyy/MM/dd HH:mm:ss+SSS';
date = '1940/01/11 18:00:00+00';
datetime(date, 'InputFormat', infmt)
ans = datetime
11-Jan-1940 18:00:00
  1 Comment
Walter Roberson
Walter Roberson on 28 Sep 2023
u, uu, ...ISO year. A single number designating the year. An ISO year value assigns positive values to CE years and negative values to BCE years, with 1 BCE being year 0.
Not sure why you say there is no such thing in the documentaiton ?

Sign in to comment.

Tags

Products


Release

R2023a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!