The correct answer was I first needed to declare the variable as a date for the sql statement:
'DECLARE @date date
set @date = cast('''+formatDateTime($Planner/Date, 'yyyyMMdd') + ''' as date)
EXECUTE dbo.sp_StoredProcedure
@date ,
@customerName ='''+$Planner/Customer+''',
@siteName ='''+$Planner/Site+''';
'
if $DateUpdated != empty
then 'UPDATE Entity1 SET Date1= Date_Updated='''+formatDateTime($Date_Updated, 'yyyy-MM-dd')+'''
else ….
Would that do the trick?
You can convert your string to a date: https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
Probably something like:
'EXEC dbo.sp_StoredProcedure
@dateStart='' & PARSE(' + formatDateTime($Planner/Date, 'yyyy-MM-dd') + ' as date) & '',
@customerName='' & '+$Planner/Customer+' & '',
@siteName='' & '+$Planner/Site+' & ''
'