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?

New contributor
Rahul soni is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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. – Tony Hinkle Mar 24 at 12:15

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.

  • I cannot pick and check manually every value of column. Is there anyway by which it can be done automatically. – Rahul soni Mar 24 at 5:33
  • 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. – HandyD Mar 24 at 5:36

Your Answer

Rahul soni is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.