Query Works Locally But Not Server

I thought I had this query licked but something must be wrong.  The query below is one of about 7 that run on a report page.  It populates a gridview and piechart.  When I run it locally everything works fine, however, when I try to run it on the test server it throws the error "Conversion failed when converting date and/or time from character string".  I know it's something in the query since I have removed the datasource from the gridview and piechart and the rest of the report runs smoothly.  

Any ideas?????


PROC [dbo].[demoAgeCount]

@username varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy'

AS


SELECT age_group
	 , count(*) AS rows
FROM
	(
	 SELECT
		 CASE
			 WHEN isdate(DOB) = 1 THEN
				 CASE

					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 < 19 THEN
						 'a. Less Than 19'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 19 AND 29 THEN
						 'b. 19-29'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 30 AND 39 THEN
						 'c. 30-39'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 40 AND 49 THEN
						 'd. 40-49'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 50 AND 59 THEN
						 'e. 50-59'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 60 AND 69 THEN
						 'f. 60-69'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 70 AND 79 THEN
						 'g. 70-79'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 BETWEEN 80 AND 89 THEN
						 'h. 80-89'
					 WHEN (convert(VARCHAR(10), (datediff(YEAR, DOB, getdate()))))
						 > 90 THEN
						 'i. Greater Than 90'
					 ELSE
						 NULL
				 END 
				 else null end  
as age_group                   
                   
                   
         FROM client 
         WHERE EXISTS( SELECT NULL 
                  FROM activity 
                 WHERE activity.charityckid=client.charityckid
		    AND client.CharityCkID = activity.CharityCkID
			 AND activity.username = @username
			 AND activity.date >= @dStart
			 AND activity.date <= @dEnd
                   ) 

       ) as T
 where age_group is not null  

group
    by age_group 
ORDER BY age_group

Open in new window

ppostonPresident/OwnerAsked:
Who is Participating?
 
Alan WarrenConnect With a Mentor Applications DeveloperCommented:
Hi pposton,

Are your date fields in the tables of type datetime or smalldatetime?

Wondering if this is sorta what you are looking for:
declare @username varchar(15) = 'Alan'
declare @dStart datetime = '2012-09-01 00:00:00' 
declare @dEnd datetime = '2012-09-05 23:59:00'

SELECT DISTINCT
        (Select COUNT(*) FROM activity a2 left join client c2 ON a2.charityckid = c2.charityckid WHERE c2.charityckid is not null and (a2.[date] >= @dStart) AND (a2.[date] <= @dEnd) AND (a2.username=a.username))  as Activities
		,a.username 
		,CASE	WHEN datediff(year, c.DOB, GETDATE()) < 19 THEN 'a. Less Than 19' 
				WHEN datediff(year, c.DOB, GETDATE()) < 29 THEN 'b. 19-29' 
				WHEN datediff(year, c.DOB, GETDATE()) < 39 THEN 'c. 30-39' 
				WHEN datediff(year, c.DOB, GETDATE()) < 49 THEN 'd. 40-49' 
				WHEN datediff(year, c.DOB, GETDATE()) < 59 THEN 'e. 50-59' 
				WHEN datediff(year, c.DOB, GETDATE()) < 69 THEN 'f. 60-69' 
				WHEN datediff(year, c.DOB, GETDATE()) < 79 THEN 'g. 70-79' 
				WHEN datediff(year, c.DOB, GETDATE()) < 89 THEN 'h. 80-89' 
				ELSE 'i. Greater Than 90' 
				END AS age_group

FROM        activity a left join 
				client c ON a.charityckid = c.charityckid 
WHERE c.charityckid is not null 
AND (a.[date] >= @dStart) 
AND (a.[date] <= @dEnd)
-- AND (a.username = @username) -- apply this to return only one row


-- Returns
Activities    username    age_group
1                Fred             g. 70-79
2                Alan             e. 50-59

Open in new window

Alan
0
 
Alan WarrenApplications DeveloperCommented:
Hi pposton,
if I run your script with the default values of the date params set the way you have them, I get the error too.
@dStart datetime = 'mm/dd/yyyy'
,@dEnd datetime ='mm/dd/yyyy'
-- Msg 241, Level 16, State 1, Line 6
-- Conversion failed when converting date and/or time from character string.

Open in new window

If I give the default values an actual date string:
@dStart datetime = '2012-09-01 00:00:00'
,@dEnd datetime ='2012-09-30 23:59:00'
Returns:
 -- age_group	rows
 -- c. 30-39	3

Open in new window

I suspect your procedure that is invoking a call to this sproc is passing in NULL for the date paramaters, then the sproc is attempting to use the date pattern that is defined as the default values.

Alan
0
 
ppostonPresident/OwnerAuthor Commented:
it seems the issue is in the convert statement.  I substituded actually values for the parameters and I still get the error (on the server, not locally).  I thought the parameters would be okay since I have used them several times before and about 5 times on the same webform I'm trying to run this one on.

Any ideas why the convert varchar would not work on the server and how I might fix it?

Thanks!
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
The error is the invalid default values.  Change those first.

The CONVERTs should work as long as the date is in a valid format for that server and its settings.


Btw, you do realize that this:

datediff(YEAR, DOB, getdate())

does NOT accurately calculate someone's age, right?

Datediff(YEAR actually just counts the number of times the date crosses from Dec 31 to Jan 1 during the range, not an actual number of "full" years.
0
 
ppostonPresident/OwnerAuthor Commented:
I'm not sure I follow you, are saying by the default values the data that is in the database?

If I run the report with date values that just include one client with a DOB of 08/08/1963 and between the dates of 09/01/2012 and 09/05/2012, it should return 1 response in the 40-49 age group.  It still gives the error even though we are looking at a single record with correct date formats.

I'm new to this, but I just don't understand why it works on my computer but not after I upload to the server?  Also, these parameters and values work just fine in about 15 other reports even other queries on the same page?

Hope not to be a bother, I'm just trying to figure out the logic of what's going wrong.

Thanks for the responses!!
0
 
ppostonPresident/OwnerAuthor Commented:
Also, what would suggest as a better solution for the datediff(YEAR, DOB, getdate())?  I had seen several other options online, but it seemed as there was no concensus.

Thanks
0
 
darjimaulikConnect With a Mentor Commented:
Hi,
When you are facing error related to date its best to verify the date format on the server.
The code/Query written by you may be accepting mm/dd/yyyy (Date Format on your system) and server has date format as dd/mm/yyyy or something else.
0
 
ppostonPresident/OwnerAuthor Commented:
This is the date format I get when running select getdate()

2012-09-05 20:41:53.220

Which is the same thing I get on my local machine
0
 
ppostonPresident/OwnerAuthor Commented:
Thanks for all the help and pointing me in the right direction.  After some trial and error I figured out a query to find and then eliminate the few rows of bad data and now everything is working well.

Thanks again!!
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.