Date Conversion Issue MS Access to SQL Server -

Date Conversion Issue MS Access to SQL Server -

i'm creating table b exisitng table a. in table have column valdate varchar , contains date. when seek create table b have function used in query given below , conversion error. table contains null values well. ms access:

((datediff("d",date(),format(replace(replace([table a].valdate,".","/"),"00/00/0000","00:00:00"),"dd/mm/yyyy")))>0)).

tables in ms access , beingness migrated 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 date using several approachs convert , format , cast end getting error below.

msg 8116, level 16, state 1, line 1 argument info type date invalid argument 1 of isdate function.

i appreciate telling me i'm missing here.

since have date info in string field have value not valid against expected date format.

copy info in sql server table , perform check , validation of content of string field.

have function try_convert can helpful when checking content of string field containing date values.

when bad info ruled out can apply 1 time again formula (hopefully) different result. improve solution create separate field appropriate datatype store date values converted string field , apply logic field.

sql-server ms-access sql-server-2012 ms-access-2010 sql-date-functions