You can't concatenate datetime value with a string.
Problem is in the line
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('+CAST(@ActivityDate as datetime)+')'
Check following -
DECLARE @ActivityDate as date
DECLARE @SqlQuery as varchar(1000)
SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('''+CAST(CAST(@ActivityDate as datetime) AS VARCHAR)+''')'
Exec(@SqlQuery)
Note that I have also added 2 single quotations before and after the value after casting it to varchar value.
I hope this was helpful :)
Update 1:
To get complete date with time you need to change the datatype of @ActivityDate to DATETIME as follows
DECLARE @ActivityDate as DATETIME
DECLARE @SqlQuery as varchar(1000)
SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('''+CAST(@ActivityDate AS VARCHAR)+''')'
Exec(@SqlQuery)
Update 2:
To get value till minute, second & milliseconds you need to use
CONVERT instead of
CAST. Note that code 113 or 13 will give us the desired result.
DECLARE @ActivityDate as DATETIME
DECLARE @SqlQuery as varchar(1000)
SET @ActivityDate=current_timestamp
SET @SqlQuery='Insert into TestDB (ActivityDate) VALUES ('''+CONVERT(VARCHAR,@ActivityDate,113)+''''
Exec(@SqlQuery)