Why you are converting your @sdate paramater of SP_FirstDistil to dd/mm/yyyy.

check this

declare @sdate date=''2014-10-29''

select convert(varchar(20),@sdate,103)

It results to 29/10/2014 and when you are passing this to the SP_Firstdistil as date it results to an error.

You can try for convert(varchar(20),@sdate,101)

Why you are converting your @sdate paramater of SP_FirstDistil to dd/mm/yyyy.

check this

declare @sdate date=''2014-10-29''

select convert(varchar(20),@sdate,103)

It results to 29/10/2014 and when you are passing this to the SP_Firstdistil as date it results to an error.

You can try for convert(varchar(20),@sdate,101)

Instead of:

Instead of:

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

use this:

use this:

SET @EndDate = DATEADD(day, DATEDIFF(day, 0, @SDate), '06:00:00')

In case of troubles, use SET DATETIMEFORMAT[^] command at the begining of SP, depending on SQL server settings.

In case of troubles, use SET DATETIMEFORMAT[^] command at the begining of SP, depending on SQL server settings.

BEGIN
    SET DATETIMEFORMAT dmy;
    --or
    --SET DATETIMEFORMAT mdy;
    --or
    --SET DATETIMEFORMAT ymd;