I have a table with a date/time column. The symbols separating the date need to be changed in order for it to work with the TimeManager plugin.

Eg. 2016:08:13 08:41:20 to 2016/08/13 08:41:20

The column name is 'datetimeor' and the table is 'ba396'

  • If you check your layer info in db manager, is your column type a datetime or a text (like char, varchar...)? – FSimardGIS Sep 21 '16 at 21:26
  • varchar (254).......................... – brink Sep 21 '16 at 21:31
  • Welcome to gis.stackexchange! Please note that a good question on this site is expected to show some degree of research on your part, i.e. what you have tried and - if applicable - code so far. For more info, you can check our faq. – underdark Jan 13 '17 at 18:35

In pure SQL you could convert your varchar field to a datetime using something like this :

CAST(SUBSTRING('datetimeor',1,4) + '-' + SUBSTRING('datetimeor',6,2) + '-' +
SUBSTRING('datetimeor',9,2) + 'T' + SUBSTRING('datetimeor',12,2) + ':' + 
SUBSTRING('datetimeor',15,2) + ':' + SUBSTRING('datetimeor',18,2) as datetime)

This would have to be tested on your system, i believe it depends on your database server type and version, etc.

  • 1
    That's a bit more complicated than I expected. I figured out how to export the date in the proper format so I don't need this. Thank you for your help. I'm not sure if I should accept this answer due to the circumstances. – brink Sep 22 '16 at 16:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.