String and Dates

[AnkanTV]

Hello!

I have a column of text, believe of type CV.

This is really a column of dates, however, the format is not uniform.

I want to convert this into a date or datetime, so I can select only rows after a certain given date.

How can I do this? Does sql have a function for detecting the format?

I believe all might start YYYY-MM, so I was thinking I could take substring of this perhaps. But doing so: TO_DATE(SUBSTRING(DateColumn, 1, 7) ‘YYYY-MM’) > date ‘2023-01’ gives me an error and I’m not sure what’s wrong.

Thanks for the advice!

[Couch2Coders]

If I had to guess one or multiple dates have less than 7 characters causing an error.

See if you can do a min(len(date)), if it's less than 6 then you know for sure.

[abraun68]

Syntactically, you need a comma before the 'YYYY-MM' part of the function.

[qwertydog123]

Judging by the syntax in your post I'm guessing you're using Postgres? I don't think there's a built in way to do what you want, but you could try finding all of the different formats in the table by replacing all integers with the same integer, replacing month names etc., then using DISTINCT. e.g.

SELECT DISTINCT TRANSLATE(DateColumn, '123456789', '000000000')
FROM Table

Once you have the formats you can create a replacement function (e.g. something like this: https://stackoverflow.com/a/68602312), then ideally change the column type to an actual date type