Depending on your regional settings, the parameter you are passing in for @TimeItemAdded might not be recognized.

You should pass the date in as:

20120720

Try CONVERT(DATETIME, @PaymentDate, 103)

104 is the German style which uses periods between the numerals, as opposed to slashes. 103 is the British/French style.

See: https://msdn.microsoft.com/en-us/library/ms187928.aspx

You can't pass function as stored procedure parameter. Use variable to store the function return-value, then you can pass the variable as parameter for SP, for example :

DECLARE @currentdate DATETIME
SET @currentdate = GETDATE()

EXEC PP_Sp_ObservationSchedule'F5B42B97-4828-4D0B-8129-0FAA37800ADB',1,1,'F5B42B97-4828-4D0B-8129-0FAA37800ADB',1,@currentdate,'abc',''
                                                                                       Notice usage of the variable^

Since you are trying to pass in table name (not in your question but mentioned in the comments) you do need dynamic sql because table names cannot be parameterized. However, you can pass in the name as a parameter to your procedure just like all the values. Then you can parameterize your dynamic sql which will eliminate all the hassle of datatype conversions and such. Something like this should be pretty close. I also added a lot of white space so this is easier to read.

ALTER PROCEDURE [dbo].[myproc]
(    
    @name varchar(50)
    , @ID bigint
    , @birthdate datetime
    , @Enabled bit
    , @married bit 
    , @employees int
    , @hiredDate datetime
    , @TableName sysname
)
AS
BEGIN 
    SET NOCOUNT ON
    DECLARE @sql AS NVARCHAR(MAX)       
    SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ID = @ID )
                     UPDATE ' + QUOTENAME(@TableName) + ' 
                     SET
                     name = @name
                    , birthdate = @birthdate
                    , Enabled = @Enabled
                    , married = @married
                    , employees = @employees
                    , hiredDate = @hiredDate
                    where ID = @ID
                ELSE
                INSERT INTO ' + QUOTENAME(@TableName) + '
                (
                    name
                    , ID
                    , birthdate
                    , Enabled
                    , married
                    , employees
                    , hiredDate
                )VALUES
                (
                    @name
                    , @ID
                    , @birthdate
                    , @Enabled
                    , @married
                    , @employees
                    , @hiredDate
                )'
EXEC sp_executesql @sql,
    N'@name varchar(50)
    , @ID bigint
    , @birthdate datetime
    , @Enabled bit
    , @married bit 
    , @employees int
    , @hiredDate datetime'
    , @name = @name 
    , @ID = @ID
    , @birthdate = @birthdate
    , @Enabled = @Enabled
    , @married = @married
    , @employees = @employees
    , @hiredDate = @hiredDate

END

103 is an ambiguous style. If you must continue with this string-heavy style, at least use unambiguous styles in your conversions.

The unambiguous styles are 112 (for just a date) and 126 (for a date and time). So:

SET @SQL ='SELECT * FROM OPENROWSET(''SQLNCLI'',
''Server=ADMIN-PC;Trusted_Connection=yes;'',
''SET FMTONLY OFF; EXEC [cheminova].[dbo].[SP_Firstdistil] "'+
    convert(varchar(20),@sdate,112)+'" '')'

Although I'd also recommend switching to using sp_executesql which allows you to pass parameters using there natural types rather than converting everything into strings.

Other points from comments, not directly relating to the problem/solution:

1) Using this many temporary tables is usually a sign that you're doing it wrong^TM - it feels like you've broken the task down into lots of little procedural steps. Instead, in SQL, what you should try to do is describe the entire task you want to do (rather than how to do it) and let the query optimizer attempt to find the best way to achieve that result.

2) Avoid using the sp_ prefix when naming procedures

3) Stop doing everything by string manipulation. For instance, this:

set @EndDate=CONVERT(varchar,DATEPART(month,@SDate))+'/'+CONVERT(varchar,DATEPART(DAY,@SDate))+'/'+CONVERT(varchar,DATEPART(YEAR,@SDate))+' 06:00:00'

which appears to be attempting to create "today at 6am" could be replaced by:

SET @EndDate = DATEADD(day,DATEDIFF(day,'20000101',@SDate),'2001-01-01T06:00:00')

Which just performs two simple pieces of date arithmetic rather than constructing an (again) ambiguous date format and hoping that SQL Server converts it back into a datetime correctly.

4) And similarly, once that date has been constructed, you can simply DATEADD(day,-1,@EndDate) to produce "yesterday at 6am" rather than doing either of those conversions again.

Got an answer from elsewhere that I will use:

SELECT top 1 multi FROM OPENQUERY([production], 'DECLARE @dt datetime SELECT @dt = GETDATE() exec proddb.dbo.spCalcProjection @dt')

This avoids having to create any additional objects in the db.