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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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