• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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

0
CodeRooster
Asked:
CodeRooster
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now