Solved

Date Parameter in SQL Stored Procedure

Posted on 2011-03-21
2
367 Views
Last Modified: 2012-08-13
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
Comment
Question by:CodeRooster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 250 total points
ID: 35183451
-- 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
 

Author Comment

by:CodeRooster
ID: 35183502
Oh that's awesome.  Worked beautifully.  Thanks!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question