Query Works Locally But Not Server

Posted on 2012-09-04
Last Modified: 2012-09-06
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'


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

					 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  
as age_group                   
         FROM client 
                  FROM activity 
                 WHERE activity.charityckid=client.charityckid
		    AND client.CharityCkID = activity.CharityCkID
			 AND activity.username = @username
			 AND >= @dStart
			 AND <= @dEnd

       ) as T
 where age_group is not null  

    by age_group 
ORDER BY age_group

Open in new window

Question by:pposton
    LVL 26

    Expert Comment

    by:Alan Warren
    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'
     -- 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.


    Author Comment

    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?

    LVL 68

    Assisted Solution

    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.

    Author Comment

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

    Author Comment

    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.

    LVL 9

    Assisted Solution

    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.

    Author Comment

    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
    LVL 26

    Accepted Solution

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


    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now