check format of every value of column in sql server

Question

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?

• Tony Hinkle: Hi Rahul--you'll probably get a better response if you can show what you've tried and then someone can explain why that doesn't work.

Answer 1

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.

• Rahul soni: I cannot pick and check manually every value of column. Is there anyway by which it can be done automatically.
• HandyD: I don't know what you mean, you can use TRY_PARSE in a SELECT query to identify the rows that do or do not convert. Use an INSERT INTO...SELECT statement to insert the rows that do convert into your second table.
• Graham: INSERT INTO TABLE_2(dob) SELECT TRY_CAST(dob AS DATETIME2) FROM TABLE_1 WHERE TRY_CAST(dob AS DATETIME2) IS NOT NULL Note: TRY_PARSE(DATETIME2, dob) would be the correct syntax. if you prefer that over TRY_CAST