check format of every value of column in sql server
You can use TRY_PARSE for this. This function will attempt to parse a supplied NVARCHAR value as a given data type, and if it fails it returns NULL.

For example, you can try the following:

select TRY_PARSE('11-23-09' AS DATETIME2)


and it returns

2009-11-23 00:00:00.0000000


But this

select TRY_PARSE('abc' AS DATETIME2)


returns NULL because it cannot parse 'abc' as a datetime value. There are other options, such as using TRY_CONVERT or TRY_CAST, however, TRY_PARSE as the advantage of allowing you to use a culture setting to parse values for a specific region/language.

For example, TRY_PARSE('11-23-09' AS DATETIME2) will parse if the default region is en-US on your SQL Server, but TRY_PARSE('23-11-09' AS DATETIME2) will fail as the NVARCHAR value is not using the US date format.

Specifying the region in the TRY_PARSE function - TRY_PARSE('23-11-09' AS DATETIME2 USING 'en-AU') - means that the string value will PARSE correctly.

You can see this behaviour in this db&lt;>fiddle.

For your specific requirement, use TRY_PARSE in the WHERE clause to identify rows in the first table that can be successfully converted to a datetime value for inserting into your second table.