I have a table_1 that has column 'dob' which has dates in nvarchar(max). Now I want to check every date whether it is in datetime format or not. The dates which are in datetime format should be copied to table_2 that has column which has dates in datetime datatype format.
Any help how can I do this?
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<>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.
