Solved

Date Parameter in SQL Stored Procedure

Posted on 2011-03-21
2
350 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
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now