Converting string column to datetime column in QGIS

Question

I have an attribute column that has a string datetime and I want to convert that to a date column. I first attempted to simply add the time column in an expression, and I only got the year/month/date, but I wanted the time as well. So I first converted it the string to a new column with format_date(), to clean up the format, and then I went ahead and used to_datetime() to convert the string into the date column. It shows up fine in the Expression dialogue but then doesn't add the hour/minute/seconds.

What am I overlooking?

Using QGIS version 3.16.1-Hannover on Windows

Answer 1

You probably use shapefile format. Otherwise it should have worked.

According to the documentation shapefile format doesn't store date and time in the same field. You need to add date and time to seperate columns or use other format like GeoPackage.

Answer 2

I had similar problem with column "IGNITIONDA" with values like "1930/11/01 00:00:00.000".

I converted this to only date column (but you can also add the time) in two steps. first you need to convert the format from string to either only date or only time or with combination with command:

format_date("IGNITIONDA" , 'yyyyMMdd')

Running this will create a column (here I named "ig_date") with only string having value "19301101". Now convert this string to the date with command to new column with date type:

to_date("ig_date",'yyyyMMdd')

Running the above commands will give the column with value type

<date: 1930-11-01>