Date Conversion Issue MS Access to SQL Server

Question

I'm creating a table B from an exisitng table A. In the table A I have a column ValDate which is varchar and contains Date. When I try to create the table B I have a function used in the query as given below and I get a conversion error. Table A contains null values as well. MS Access:

((DateDiff("d",Date(),format(Replace(Replace([Table A].ValDate,".","/"),"00/00/0000","00:00:00"),"dd/mm/yyyy")))>0)).

Tables were in MS Access and are being migrated to SQL Server 2012.

SQL Server:

((DATEDIFF(day,FORMAT( GETDATE(), 'dd-MM-yyyy', 'en-US' ),FORMAT( ValDate, 'dd-MM-yyyy', 'en-US' ))>0)) 

or

((DateDiff(day,GETDATE(),Format(Replace(Replace([TableA].[ValidFrom],'.','/'),'00/00/0000','00:00:00'),'dd/mm/yyyy')))

I tried converting the date using several approachs like Convert , Format and Cast but I end up getting error as below.

Msg 8116, Level 16, State 1, Line 1 Argument data type date is invalid for argument 1 of isdate function.

I would really appreciate someone telling me what I'm missing here.

Answer 1

since you have date data in a string field is very likely you have some value that is not valid against your expected date format.

copy the data in a sql server table and then perform check and validation of the content of the string field.

have a look to the function try_convert that can be helpful when checking the content of the string field containing the date values.

when bad data is ruled out you can apply again your formula with (hopefully) a different result.
a better solution would be to create a separate field with appropriate datatype to store date values converted from the string field and apply your logic to that field.