[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
387 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 500 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 500 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
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…

649 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