Link to home
Create AccountLog in
Avatar of mcai9mh3
mcai9mh3

asked on

Overflow error with DateDiff in Reporting Services, but not in SQL2005

Hi,

I am trying to run the following query:

SELECT count(*) from opencall
      where
            DATEDIFF(minute, logdate, closedate) < 1140
            and closedby is not null
            and closedby <> ' '
            and Cast(logdate AS datetime) > '29/02/2008'

This runs ok when run in SQL server management studio, but when I run it in reporting services (MS Visual Studio) I get the following error:

TITLE: Microsoft Report Designer
------------------------------
An error occurred while reading data from the query result set.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
------------------------------
ADDITIONAL INFORMATION:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (Microsoft SQL Server, Error: 242)


Any ideas?

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mcai9mh3
mcai9mh3

ASKER

Thanks for the solutions.
It turns out that I had not set visual studio to European time format.

adding this line at the top of the code solved it.

SET DATEFORMAT dmy;

Thanks for your help!