Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Date Parameter in SQL Stored Procedure

Posted on 2011-03-21
2
Medium Priority
?
375 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

670 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