How to check TIMESTAMP format in SQL?-CodePudding

What's the best way to check if the VARIANT TYPE with JSONs with value similar to TIMESTAMP_NTZ has correct format?

For example, this is the correct format that I would like to have

 2020-12-26T12:12:11.215581Z

but there are times when it looks different in database, like this

2021-11-26T12:12:11.215581Z[UTC]

I would like to detect records which are in a different format than the reference.

I tried with simple LIKE, but it omits formats that could be different than this.

LIKE '%[UTC]%'

CodePudding user response：

You may use a regular expression to check the equality of the desired format. I will extend this answer if the rdbms is known.

[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{6}Z

MySQL & SQLite:

SELECT * FROM table 
WHERE timestamp not REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{6}Z';

Oracle:

SELECT * FROM table 
WHERE not REGEXP_LIKE(timestamp, '[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{6}Z');

CodePudding user response：

If all the records you want to detect come in addition to the end of the format that should be then you can use length.

Basically:

WHERE NOT len(column_name)=27

27 because your correct format 2020-12-26T12:12:11.215581Z has 27 characters.