k.horde k.horde - 2 months ago 6
SQL Question

SQL how to pull out data which has commas without them?

In the database there is date put into fields with commas like

20,151,112
(yy,yym,mdd)
which stands for date
12-11-2015
.

I need to take out the date and filter it so that I have only records from a period of time.

Do you know how I can pull out the data without the commas so I have integers which I can compare then?

Answer

Remove the commas using REPLACE, and then convert to a date using TIMESTAMP_FORMAT and DATE:

DATE(TIMESTAMP_FORMAT(REPLACE(col, ',', ''), 'YYYYMMDD'))

e.g.

SELECT DATE(TIMESTAMP_FORMAT(REPLACE('20,151,112', ',', ''), 'YYYYMMDD')) AS "theDate"
FROM yourTable
Comments