Invalid seconds in timestamp field in SQL

Question

I've a timestamp field in a table where second values SUBSTR(col,13,2) are 60+ in some places. I want to update invalid second portion of the timestamp field and convert this kind of data into valid timestamp format DDMMYYYYHHMISS.

Sample data:

CREATE VOLATILE TABLE TEST (COL VARCHAR(50)) ON COMMIT PRESERVE ROWS;

INSERT INTO TEST (04012022000010);
INSERT INTO TEST (31012022000066);
INSERT INTO TEST (02012021000067);

    COL
1   31012022000066
2   02012021000067
3   04012022000010

Answer 1

I think that this is what you are needing. We convert the string without the seconds to DATETIME and add the number of seconds. I give 2 versions because the DATETIME format requested is not the standard ISO format The first request uses the date format as requested in the question. I give 2 versions because I don't know whether your local settings modify the automatic functions.

DDMMYYYYHHMISS

SELECT CAST(
   CONCAT(
      SUBSTRING(COL,1,12),'00'
      ) AS TIMESTAMP) 
   + INTERVAL SUBSTRING(COL,11,2) second
FROM TEST;

We convert the input to ISO and then format the result to requested format.

Input:  YYYY-MM-DD HH:MI:SS
Output: DDMMYYYYHHMISS

SELECT CAST(
   CONCAT( 
      SUBSTRING(COL,5,4),'-',
      SUBSTRING(COL,1,2),'-',
      SUBSTRING(COL,3,2),' ',
      SUBSTRING(COL,9,2),':',
      SUBSTRING(COL,11,2),':00'
      ) AS TIMESTAMP) 
   + INTERVAL SUBSTRING(COL,11,2) 
   FORMAT 'DDMMYYYYHHMISS' 
   AS corrected_date
FROM TEST;