SQL - Looking for records that have a transaction date between parameter and first of week - Error Converting data type varchar to numeric

Hi Experts -
I'm trying to CASE a value for hours that fall between a date given as a parameter and the effective first day of the timesheet period - but I seem to be having an issue with the format of either the date in the comparison statement or the converted date to determine the first day of the week.

The error is Error Converting data type Varchar to Numeric

 
DECLARE
    @Project VARCHAR(30), @Phase VARCHAR(7), @Task VARCHAR(7),
@custThroughDate DATETIME,

Set @Project = '01080901'
Set @Phase = '0301'
Set @Task = '00'
Set @custThroughDate = '5/29/2009'



Select 
					Project as Project, 
					Phase as Phase, 
					Task as Task,
					vwAA_BRRecTypeBillStatus.RecType, 
					vwAA_BRRecTypeBillStatus.DetailGrouping,
					TransDate,
			
					CASE WHEN RecType IN ('Labor','Unposted') AND
						  Transdate >= DATEADD(dd, - DATEPART(dw, CONVERT(varchar, @custThroughDate, 101)), CONVERT(varchar, @custThroughDate, 101)) 
						AND Transdate <= @custThroughDate 
						THEN RegHrs + OvtHrs + SpecialOvtHrs ELSE '' END AS CurrentWeekHrs
					
					FROM
					vwAA_BRRecTypeBillStatus where Project = @Project AND Phase = @Phase AND Task = @Task

Open in new window

ScudboyAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
That should have been:
CASE 
    WHEN RecType IN ('Labor', 'Unposted')
	AND Transdate >= DATEADD(dd, -DATEPART(dw, CONVERT(datetime, @custThroughDate, 101)), CONVERT(datetime, @custThroughDate, 101))
	AND Transdate <= CONVERT(datetime, @custThroughDate, 101) THEN RegHrs + OvtHrs + SpecialOvtHrs
    ELSE ''
END AS CurrentWeekHrs

Open in new window

0
 
Anthony PerkinsCommented:
Try it this way:
CASE 
      WHEN RecType IN ('Labor', 'Unposted')
                  AND Transdate >= DATEADD(dd, -DATEPART(dw, CONVERT(datetime, @custThroughDate, 101)), CONVERT(datetime, @custThroughDate, 101))
                  AND Transdate <= @custThroughDate THEN RegHrs + OvtHrs + SpecialOvtHrs
      ELSE ''
END AS CurrentWeekHrs

Open in new window

0
 
Anthony PerkinsCommented:
This might be a better option:
Change:
@custThroughDate = '5/29/2009'

For:
 @custThroughDate = CONVERT(datetime, '5/29/2009', 101)

And then:
CASE 
	    WHEN RecType IN ('Labor', 'Unposted')
                  AND Transdate >= DATEADD(dd, -DATEPART(dw, @custThroughDate), @custThroughDate)
                  AND Transdate <= @custThroughDate THEN RegHrs + OvtHrs + SpecialOvtHrs
             ELSE ''
        END AS CurrentWeekHrs

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
for issues around dates, this article should be read:
http://www.experts-exchange.com/A_1499.html
0
 
ScudboyAuthor Commented:
Hey guys -
I feel like a big dummy - found this earlier today but have been so buried I haven't been able to update.  
The error was being thrown by THEN RegHrs + OvtHrs + SpecialOvtHrs ELSE ''
My ELSE statement needed to be a numeric value, or 0.

You know, just when I start feeling good about SQL I pull a dummy one.  
Thank you all for responding!  It is sincerely appreciated, and while you all can fix SQL, you still can't fix stupid, or in this case, stupid me.

Cheers.
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.