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
381 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 39
Building JSON Results Table FROM DB 9 33
Checking for column changes SQL 2014 4 21
Applying Roles in Common Scenarios 3 17
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 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