Solved

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

Posted on 2011-03-10
5
383 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Scudboy
[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
  • 3
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35095118
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 35095149
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35095223
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 35095385
for issues around dates, this article should be read:
http://www.experts-exchange.com/A_1499.html
0
 

Author Comment

by:Scudboy
ID: 35104176
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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