Date Parameter in SQL Stored Procedure

I am creating a stored procedure that uses a date as one of the parameters.  When I run the query outside of the stored procedure it executes fine.  However, when I try to run it as a stored procedure I get the following error -
 
  Msg 241, Level 16, State 1, Procedure usp_missTimeDetail, Line 0
  Conversion failed when converting datetime from character string.

One of the parameters I am passing in is a date in the format of '01/01/2011'.  This works fine when I run the query outside of the stored procedure.  Any thoughts?

/*
Name: usp_missTimeDetail
Description:  give a breakdown of missing time per employee for each day in a given period
Author:  *************
Modification Log: Change

Description                  Date         Changed By
Created procedure            3/21/2011    Tamer Hassan
*/

create procedure usp_missTimeDetail 

@FDate varchar(10) = '01/01/2011',
@ToDate datetime = getdate,
@post_date datetime = getdate,
@hoursThreshold int = 40

AS
SET NOCOUNT ON

truncate table missing_time_detail

declare @FromDate datetime
set @FromDate = convert(datetime, @FDate)

---- DEBUG variables -----------------------------------------------
--declare @FromDate as DATETIME
--declare @ToDate as DATETIME
--declare  @post_date datetime
--declare @hoursThreshold int

--set @hoursThreshold = 40
--set @FromDate = '01/01/2011'  
--set @ToDate =	getdate()		--'2/28/2011'
--select @post_date = getdate() --convert(datetime,convert(char(8),getdate(),1))
--------------------------------------------------------------------

declare @payroll_date datetime
select @payroll_date = convert(datetime, substring(convert(char(8),getdate(),1),1,3)+ '01'+substring(convert(char(8),getdate(),1),6,3)   )

-- temptb1: select initial group of employees to report on..

declare @temptb1 table (empl_uno int, persnl_typ_code char(1), hire_date datetime, _payroll_term_date datetime)

insert into @temptb1 (empl_uno, persnl_typ_code, hire_date, _payroll_term_date)
select	 t1.empl_uno
	,t1.persnl_typ_code
	,t1.hire_date
	,t1._payroll_term_date	

from   	REPORT_cms.dbo.hbm_persnl t1 
		join 	REPORT_cms.dbo.tbm_persnl 	as t6 on t1.empl_uno=t6.empl_uno
		
where  	t1.position = 'FT'
and 	t1.employee_code not in ('02678','09996') 
and 	t1.employee_code not like '[a-z]%' 
and		t1.work_type_code not in ('LOA','SPCAS')
and 	t1.persnl_typ_code in ('A','C','D','E','G','H','J','K','L', 'Q')  --in (@pTypeCode) -- control these options in the pTypeCode filter file
and     t1.hire_date <= @post_date
and 	(t1._payroll_term_date is null or (t1._payroll_term_date >= @payroll_date))                                          

and 	t6.partime_pcnt >= 100

-- temptb2: build data dataset from calendar table
declare @temptb2 table (date datetime)
insert into @temptb2
select	t1.date
from	REPORT_cms.dbo._calendar t1 
where 	t1.date between @FromDate and @ToDate   
			
-- temptb3: 
declare @temptb3 table (
			empl_uno int, 
			persnl_typ_code char(1), 
			hire_date datetime, 
			_payroll_term_date datetime,
			date datetime,
			tran_date datetime,
			work_hrs money,
			billable money,
			non_billable money,
			primary key (empl_uno, tran_date),	
			unique clustered(empl_uno, tran_date)
			)			

insert into @temptb3		
select	 t1.* --personl_typ_code, hire date, payroll term date
	,t2.date
	,t2.date as tran_date
	,convert(money,0.00) as work_hrs
	,convert(money,0.00) as billable
	,convert(money,0.00) as non_billable

from	@temptb1 as t1
	cross join @temptb2 as t2 --date records for all dates between TO & FROM dates


-- temptb4: ..join tat_time table and get hours 
declare @temptb4 table (
			empl_uno int, 
			persnl_typ_code char(1), 
			hire_date datetime, 
			_payroll_term_date datetime,
			date datetime,
			tran_date datetime,
			work_hrs money,
			billable money,
			non_billable money
			primary key (empl_uno, tran_date),
			unique clustered (empl_uno, tran_date)
			)

insert into @temptb4
select	 t1.empl_uno
	,t1.persnl_typ_code
	,t1.hire_date
	,t1._payroll_term_date
	,t1.date
	,t1.tran_date
	,sum(isnull(t2.base_hrs,0.00)) as work_hrs
	,billable=sum(case when (t2.billable_flag ='B')then (isnull(t2.base_hrs,0.00)) end)
	,non_billable=sum(case when (t2.billable_flag <>'B') then (isnull(t2.base_hrs,0.00)) end )
from	@temptb3 as t1 
	left outer join REPORT_cms.dbo.tat_time as t2 on t1.empl_uno = t2.tk_empl_uno and t1.tran_date = t2.tran_date -- nice
where 
	t2.tran_date between @FromDate and @ToDate
and	t2.post_date <= @post_date
group by 
	t1.empl_uno
	,t1.persnl_typ_code
	,t1.hire_date
	,t1._payroll_term_date
	,t1.date
	,t1.tran_date

update t1
set  	t1.work_hrs = t2.work_hrs
 	,t1.billable = t2.billable
 	,t1.non_billable = t2.non_billable
from 	@temptb3 as t1
	join @temptb4 as t2 on t1.empl_uno = t2.empl_uno and t1.tran_date = t2.tran_date


--update t1
--set  	t1.tran_date = @FromDate
 	
--from 	@temptb3 as t1
--where	t1.work_hrs + t1.billable + t1.non_billable = 0

--sp_help @temptb3
declare @temptb5 table (
			empl_uno int, 
			persnl_typ_code char(1), 
			hire_date datetime, 
			tran_date datetime,
			_payroll_term_date datetime,
			date datetime,			
			work_hrs money,
			day varchar (10),
			billable money,
			non_billable money			
			)

set forceplan on   --?
insert into @temptb5
select	 empl_uno
	,persnl_typ_code
	,hire_date
	,tran_date
	,_payroll_term_date
	,date
	,work_hrs
	,day=case when (datepart(dw,date) in (1,7)) then 'weekend' else 'weekday' end
	,billable
	,non_billable

from 	@temptb3
where   date >= hire_date                                  

declare @temptb6 table (
			empl_uno int, 
			persnl_typ_code char(1), 
			hire_date datetime, 
			tran_date datetime,
			_payroll_term_date datetime,
			date datetime,			
			work_hrs varchar(10),
			day varchar (10),
			billable money,
			non_billable money,
			missing_hrs  money	
			)
			
insert into @temptb6
select	 empl_uno
	,persnl_typ_code
	,hire_date
	,_payroll_term_date
	,tran_date
	,date
	,day
	,work_hrs=case when (sum(work_hrs)is null) then 0.00 else sum(work_hrs) end
	,billable_hrs=case when (sum(billable)is null) then 0.00 else sum(billable) end
	,non_billable_hrs=case when (sum(non_billable)is null) then 0.00 else sum(non_billable) end
	,missing_hrs=case when (persnl_typ_code <= 'N' and sum(work_hrs) is not null 
				and sum(work_hrs) >= 8.00 and (day not in ('weekend'))    
                                and (date <= _payroll_term_date or _payroll_term_date is null)) then 0.00 
			when (persnl_typ_code <= 'N' and sum(work_hrs) is not null and sum(work_hrs) < 8.00 and  
                                      (day not in ('weekend')) and (date <= _payroll_term_date or                     
                                      _payroll_term_date is null)) then (8.00-sum(work_hrs)) 
			when (persnl_typ_code <= 'N' and sum(work_hrs) is null and (day not in ('weekend')) and 
					(date <= _payroll_term_date or _payroll_term_date is null)) then 8.00 
			when (persnl_typ_code >= 'P' and sum(work_hrs) is not null and sum(work_hrs) >= 7.00 and 
					(day not in ('weekend')) and (date <= _payroll_term_date or _payroll_term_date is null)) then 0.00 
			when (persnl_typ_code >= 'P' and sum(work_hrs) is not null and sum(work_hrs) < 7.00 and (day not in ('weekend')) 
					and (date <= _payroll_term_date or _payroll_term_date is null)) then (7.00-sum(work_hrs)) 
			when (persnl_typ_code >= 'P' and sum(work_hrs) = null and (day not in ('weekend')) and 
					(date <= _payroll_term_date or _payroll_term_date is null)) then 7.00 end 
 
from 	@temptb5
group by 
	empl_uno
	,persnl_typ_code
	,hire_date
	,_payroll_term_date
	,tran_date
	,date
	,day	
	
	  ---- detail data - missing for each day ----------------------------------		  
		
		insert into rptuser.missing_time_detail (empl_uno, persnl_typ_code, date, daily_missing, tot_missing)				
		select empl_uno,
				persnl_typ_code,
				date,
				missing_hrs,
				tot_missing
				
		from(		
				  select *, sum(missing_hrs) over (partition by empl_uno) tot_missing
				  from @temptb6			
				  where missing_hrs > 0
				  
			  ) qry
			  
		where tot_missing > @hoursThreshold	
		 and not exists ( -- avoid duplicates in the same day
				select * from missing_time_detail mtd 
					where qry.empl_uno = mtd.empl_uno and 
							qry.date = mtd.date
						)
		  
		order by tot_missing desc, empl_uno
	


--   select * from missing_time_detail

Open in new window

CodeRoosterAsked:
Who is Participating?
 
knightEknightCommented:
-- The problem is with params 2 and 3 -- you can't assign them to 'getdate' ... try this:

create procedure usp_missTimeDetail

@FDate varchar(10) = '01/01/2011',
@ToDate datetime = null,
@post_date datetime = null,
@hoursThreshold int = 40

AS
SET NOCOUNT ON

select @ToDate = isNull(@ToDate,getdate())
select @post_date= isNull(@post_date,getdate())


0
 
CodeRoosterAuthor Commented:
Oh that's awesome.  Worked beautifully.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.