sql server - Sql Overflow error, Date must be between 1/1/1753 and 12/12/9999 -

from aspx code behind date default coming 1/1/0001 00:00:00. problem code runs fine on our network when deployed client in form of patch , getting exception. in cs code, had check same, dont know why not working on client.

so restrict sp itself. parameter accepting value in codebehind datetime , in sp parameter datetime. when try change varchar() gives me exception wherever has been used.

kindly note cant change code in cs anymore. need sql end solution in sp itself.

below sp. tried.

create proc up_rem_crud_salesperson_target    (    @p_opmode int =null,    @p_personcd int,    @p_targetrate int = null,    @p_fromdate varchar(20)=null,  // problem here param, &  @p_todate varchar(20)=null,    // this, both datetime  @p_broker_booking_percentage decimal(18,0) = 0,    @p_lmby varchar(50)=null,    @p_execstatus int output   )       begin      declare @v_salary decimal(18,0)     declare @v_target decimal(18,0)     declare @v_enddate date =( getdate()+ 36500) ;     //tried check not working   if @p_fromdate='1/1/0001 00:00:00'          begin             set @p_fromdate = '1/1/1753 00:00:00'         end      if @p_fromdate ='1/1/0001 00:00:00'          begin             set @p_fromdate = '12/12/9999 23:59:59'         end    -- step 1: fetch gross salary of sales person*/      set @v_salary  = ( select top(1)  bd.grosssalary           rem_m_saleperson sp            inner join  hr_emppayslipmas bd              on sp.personcd = bd.empcode   inner join hr_empmas em             on sp.personcd = em.empcode          em.empcode=@p_personcd          order bd.monthperiodcode  desc  )      -- step 2: calculate target of sales person using     -- target = rate* salary       set @v_target = (@p_targetrate * @v_salary);      -- step 3: create target record sales person in table rem_t_salesperson_target       if @p_opmode =1      begin       insert rem_t_salesperson_target(salespersonid,targetinfigures,targetachievedinfigures,targetstartdate,targetenddate,lmby,salestargetrate, broker_booking_percentage )       values (@p_personcd,@v_target,0,getdate(),@v_enddate,@p_lmby,@p_targetrate, @p_broker_booking_percentage)      end      -- step 4: fetch records based on booking        if @p_opmode = 4      begin        declare @target_achieved decimal(18,2)        declare  @direct_sales decimal(18,0)         declare @v_sale_type varchar(1)         declare @mixed_sales decimal(18,0)        declare @total_direct decimal(18,0)        declare @total_mixed decimal(18,0)         set @target_achieved =0               set @direct_sales =0               set @mixed_sales =0         set @total_direct =0              set @total_mixed =0               /*        if date , dates provided find sales made individually sales person        , sales in brokers involved, in between date supplied.        */        if @p_fromdate not null , @p_todate  not null         begin           -- total sales made sales person individually           set @direct_sales = (select sum(a.netbasicprice)                 rem_t_applicanthdr  inner join rem_t_salesperson_target b                          on a.spersoncd = b.salespersonid                 a.spersoncd=@p_personcd , (a.appdt between @p_fromdate  , @p_todate) , (rtrim(ltrim(a.agentcd))='' ) )            -- total sales made sales person alongwith agents           set @mixed_sales = (select sum(a.netbasicprice)                rem_t_applicanthdr  inner join rem_t_salesperson_target b                   on a.spersoncd = b.salespersonid                a.spersoncd=@p_personcd ,   (a.appdt between @p_fromdate  , @p_todate) , (rtrim(ltrim(a.agentcd))<>'') )            -- if sale type direct pick amount           if @direct_sales > 0           begin            set @v_sale_type = 'd'            set @total_direct = @direct_sales           end           -- if sale made agent involved pick 20% of sale           if @mixed_sales > 0            begin             set @v_sale_type ='m'            set @total_mixed = (20*@mixed_sales)/100;             end           /*          calculate total target achieved sum of individual sale , agent based sale          */          set @target_achieved =   (isnull(@total_direct,0)+isnull(@total_mixed,0));            /*create table of target achievement report relevant details*/           select a.empname sales_person_name,@target_achieved target_achieved,@total_direct directsale,@total_mixed mixedsale,b.targetinfigures,b.salestargetrate,            case              when isnull((@target_achieved - targetinfigures),0) < 0 'below target'             else convert(varchar, isnull((@target_achieved - targetinfigures),0))             end target_difference          hr_empmas             inner join  rem_t_salesperson_target b           on a.empcode = b.salespersonid          a.empcode = @p_personcd                /*target achievement*/          select a.appno,convert(date,a.appdt,103)as appdt,c.prjname,a.applicant,a.brokerageamt,a.brokarage,a.brokassamt,a.brokon, a.netbasicprice,             case when a.agentcd <>'' 'broker sale' else 'direct sale' end sale_type          rem_t_applicanthdr inner join rem_t_salesperson_target b               on a.spersoncd = b.salespersonid               inner join rem_m_prjhdr c on c.prjcd =a.prjcd          a.spersoncd = @p_personcd ,  (a.appdt between @p_fromdate , @p_todate)             /* incentive calculation  */             declare @incentive decimal(18,0), @counter int          --set @counter  = (select count(*) rem_t_applicanthdr  inner join rem_t_salesperson_target b          --     on a.spersoncd = b.salespersonid           --      a.spersoncd = @p_personcd ,  ((convert(date,@p_fromdate,102) >= convert(date,targetstartdate,102)) , (convert(date,@p_todate,102) <= convert(date,targetenddate,102))))          --if @counter > 0          -- begin          --    set @incentive = isnull(@counter,0) * 2500;          -- end           set @incentive = (select  sum(case when discount  = 0.00 0.01 * netbasicprice                           when discount <= 0.01 0.0075 * netbasicprice                     when discount <= 0.02 0.0050 * netbasicprice                     else 0.0025 * netbasicprice                     end                   +                   case when agentcd <> 0       2500.0                    else    0.0       end)  incentive                   rem_t_applicanthdr                     inner join rem_t_salesperson_target b                                         on a.spersoncd = b.salespersonid                   a.spersoncd = @p_personcd , (a.appdt between @p_fromdate , @p_todate)                  group spersoncd                         )               select isnull(@incentive,0) totalincentive, isnull(@direct_sales,0) totalds, isnull(@mixed_sales,0) totalms, b.targetinfigures, isnull(@target_achieved,0) totalta             rem_t_applicanthdr                  inner join rem_t_salesperson_target b                 on a.spersoncd = b.salespersonid                  inner join hr_empmas c                  on a.spersoncd = c.empcode                a.spersoncd = @p_personcd , (a.appdt between @p_fromdate , @p_todate)           end        else         begin          select a.appno,a.appdt,c.prjname,a.applicant,a.brokerageamt,a.brokarage,a.brokassamt,a.brokon, a.netbasicprice,             case when a.agentcd <>'' 'broker sale' else 'direct sale' end sale_type          rem_t_applicanthdr               inner join rem_t_salesperson_target b                on a.spersoncd = b.salespersonid               inner join rem_m_prjhdr c                on c.prjcd =a.prjcd          1=0         end      end    end   

you repeating block twice:

if @p_fromdate ='1/1/0001 00:00:00'  begin     set @p_fromdate = '12/12/9999 23:59:59' end 

it should @p_todate second time

second i'll using varchar dates horrible.

third asp.net-side should use datetime? , not datetime, default value null.

another option use datetime2 parameters of query. support full range of .net datetime. quick fix, isn't solving real problem shouldn't passing 1/1/0001 around!