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.

Hooked

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.

Scrofula

Thanks Paolo for the quick reply , I did try "try_convert" function. I also verified the ValDate column is never null and sometimes as incorrect date in right format "00.00.0000" – Hooked

what's the outcome of try_convert? it always produce the correct and expected result for each and every row in the source dataset? what about applying each element of the formula one a time? – Scrofula

I got it none of my columns are getting converted , I tried this select TRY_CONVERT(datetime, '[ValidDate') AS Result from Table B. I don't understand why ? – Hooked

Are you sure the column in the Access table is text? If you migrate the table to SQL server using SSMA then SSMA will OFTEN choose the NEWER date/time formats, but if you use the standard SQL driver (ships with windows), then the LINKED table to SQL server will show text/char as a data type for that column which is likely what you do NOT want. Are you testing/trying these new queries from SQL server, or via linked tables in the Access front end? Because of this issue, you likely want to link Access to SQL server using the newer native 11 odbc driver (and then those columns will appear as date) – Trefoil

The tables are being migrated from Access to SQL Server through SSMA. I have recreated the tables with dateformat to solve this issue. – Hooked