arithmetic overflow error converting expression to data type smalldatetime

when you comment out this line

dbo.fnGetDateDiffAsYMD(DOB,GETDATE()) AS Age

do you get the same error? if yes comment out this line

CONVERT(nvarchar (10), DOB, 103) AS [Date Of Birth],

this is to test which line is failing...

@HainKurt
I get the error with the "dbo.fnGetDateDiffAsYMD(DOB,GETDATE()) AS Age" part removed, just trying it with the otherpart removed instead.

Siv

I still get the error if the CONVERT(nvarchar (10), DOB, 103) AS [Date Of Birth], is removed as well. So the CONVERT in teh WHERE clause may be the problem??

@HainKurt
Tried it with both parts you mentioned removed and it still errors so it is definitely a CONVERT that is causing the issue not the call to the function.

Siv

What are the regional settings on the computer that has error vs the computers that work?

@CodeCruiser,
I checked that first, that was my original thought that they were set US and the SQL Server is UK (we are in the UK), but I am afraid the machines are all UK and I checked the specific date formating and that was OK to.  I even checked the Vista box that was running it without error and it was set UK as well (just in case teh server was behaving US and the Vista box was set US by coincidence)?

I also removed and then reinstalled the dot net framework 4.0 as well and it makes no difference. On the machine I am doing the tests I also ran all Windows updates and checked that the SP was 3 and it was.

Siv

For testing, comment out the Where clause and see what is the varchar out of this expression

CONVERT( nvarchar (4), YEAR( GETDATE() ) ) + '-' + CONVERT( nvarchar (2), MONTH(DOB) ) + '-' + CONVERT( nvarchar (2), DAY(DOB) ) )

The question is, why are you composing the date string in yyyy-mm-dd format? Why not dd/mm/yyyy? Also Is DOB varchar or datetime?

@CodeCruiser

I am doing the date in yyyy-mm-dd format as I find that I have had troubles using UK format in VB.NET as internally it uses US format and I am nver sure what is being presented to SQL Server as date formatting often catches me out.  This method of using yyyy-mm-dd does seem to avoid the issues and I have used it for a long time without causing this particular problem.

If I comment out the CONVERT stuff above I don't get the error so that is where the issue is.

I will post back in a minute with the varchar of teh conversion.

@CodeCruiser

I lifted the relevant bit of the code and ran it as a select like this:

SELECT CONVERT( smalldatetime, ( CONVERT( nvarchar (4), YEAR( GETDATE() ) ) + '-' + CONVERT( nvarchar (2), MONTH(DOB) ) + '-' + CONVERT( nvarchar (2), DAY(DOB) ) ) ) FROM Person

And get these results (not a full list)

2011-12-24 00:00:00
2011-06-29 00:00:00
2011-05-27 00:00:00
2011-11-18 00:00:00
2011-12-30 00:00:00
2011-04-10 00:00:00
2011-09-17 00:00:00
2011-08-10 00:00:00

As you can see it is getting the time part which isn't held in a smalldatetime, which I find strange as the CONVERT at the start of the expression is supposed to be stripping that away, unless the SQL 2005 editor is just formatting it on screen as a dat rather than a "smalldateTime"??

Siv

The smalldatatime does include the time part. Another thing I found out with experiment is that

Convert(Date, '24/01/2011', 103) = GetDate()

returns false!

@CodeCruiser,
What I meant was not all of the time part (ie milliseconds).

Regarding the test code you did, I think that was what forced me to use the CONVERT functions as I was not getting equality comparing the concocted date and GetDate() without first converting it to smalldatetime.

Siv

@CodeCruiser
I ran this in SQL Server  Management Studio:

DECLARE @D1 smalldatetime
DECLARE @D2 smalldatetime
SET @D1= Convert(SmallDatetime, '24/01/2011', 103)
SET @D2= CONVERT(smalldatetime,GetDate(),103)
Select @D1 , @D2

And get this result:
2011-01-24 00:00:00      2011-01-24 17:34:00

Which probably explains why they are not equal as it's incluing the time element.

Yeah. I store my dates as strings in dd/mm/yyyy format to avoid these problems.

What I am trying to do is concoct a date in 2011 based on the staff member's date of birth, so I take the day and month from the "DOB" field and tack on the current year when the query is being run to give me a date in string format. I then ask it to check if any of the concocted dates of Birth fasll within the date range specified by the user.  Eg if the user inputs 20 as the period to check ahead for upcoming DOBs it will check if teh concocted date is within the period GetDate() to GetDate()+20 returning the resullts of teh select.

It works fine of Vista and Windows 7 boxes but fils with the overflow error on XP boxes????

use DateSerial(year, month, day)

CONVERT( smalldatetime, ( CONVERT( nvarchar (4), YEAR( GETDATE() ) ) + '-' + CONVERT( nvarchar (2), MONTH(DOB) ) + '-' + CONVERT( nvarchar (2), DAY(DOB) ) ) )
BETWEEN GETDATE() AND DATEADD(day,param1,GETDATE())

-->

dateserial(YEAR(GETDATE()), MONTH(DOB), day(DOB)) BETWEEN GETDATE() AND DATEADD(day,param1,GETDATE())

@HainKurt,
This code is SQL TRANSACTSQL code stored in a text file that is processed by the VB App not SQL run from within VB so I can't use DateSerial.  

What I do is read in the SQL code from the text file (I call them .Rep files) this is done so that I can quickly supply new reports without having to recompile my VB App.

Unless DateSerial is used in TRANSACT SQL?

When I run it I get "DateSerial" is not a recognized built-in function name. instead of the Overflow error.

I just tried this:

SELECT [PersonID] as [Staff No],
[Title] + ' ' + [Forename] + ' ' + [Surname] as [Staff Name],
HouseName,
NumberAndStreet,
VillageOrSuburb,
Town,
County,
PostCode,
CONVERT(nvarchar (10), DOB, 103) AS [Date Of Birth], dbo.fnGetDateDiffAsYMD(DOB,GETDATE()) AS Age
FROM [Person]
WHERE CONVERT( smalldatetime, ( CONVERT( nvarchar (4), YEAR( GETDATE() ) ) + '-' + CONVERT( nvarchar (2), MONTH(DOB) ) + '-' + CONVERT( nvarchar (2), DAY(DOB) ) ) ) BETWEEN CONVERT(smalldatetime,GETDATE()) AND CONVERT(smalldatetime, DATEADD(day,20,GETDATE())) AND
RecordIsDeletedYN=0 AND
DOB != '01/01/1900'
ORDER BY DOB;

And this also gives the original error!?

I also installed the application on the SBS 2003 box itself and ran the report and that doesn't error?

So it is just XP?

I am struggling.

Siv

@HainKurt

I modified your code slightly to make it work in SQL Server 2005:

SELECT CONVERT(nvarchar (10), DOB, 103) AS [Date Of Birth],
CONVERT( nvarchar (4), YEAR(GETDATE())) AS yyyy,
CONVERT( nvarchar (2), MONTH(DOB)) AS mm,
CONVERT( nvarchar (2), DAY(DOB)) AS dd  
FROM [Person]
WHERE RecordIsDeletedYN=0 AND
DOB != '01/01/1900'
ORDER BY yyyy,mm,dd;


Date Of Birth yyyy            mm               dd
01/01/1964      2011      1      1
11/01/1974      2011      1      11
23/01/1957      2011      1      23
06/01/1961      2011      1      6
06/01/1982      2011      1      6
15/10/1963      2011      10      15
15/10/1946      2011      10      15
15/10/1965      2011      10      15
25/10/1956      2011      10      25
25/10/1969      2011      10      25
04/10/1956      2011      10      4
11/11/1963      2011      11      11
18/11/1982      2011      11      18
02/11/1966      2011      11      2
24/11/1981      2011      11      24
29/11/1973      2011      11      29
09/11/1954      2011      11      9
10/12/1960      2011      12      10
16/12/1965      2011      12      16
20/12/1987      2011      12      20
23/12/1972      2011      12      23
23/12/1971      2011      12      23
24/12/1977      2011      12      24
30/12/1958      2011      12      30
05/12/1970      2011      12      5
09/12/1978      2011      12      9
09/12/1964      2011      12      9
02/02/1968      2011      2      2
21/02/1972      2011      2      21
23/02/1986      2011      2      23
10/03/1973      2011      3      10
10/04/1960      2011      4      10
12/04/1987      2011      4      12
12/04/1980      2011      4      12
18/04/1972      2011      4      18
24/04/1957      2011      4      24
27/04/1974      2011      4      27
06/04/1971      2011      4      6
08/04/1961      2011      4      8
11/05/1956      2011      5      11
14/05/1968      2011      5      14
15/05/1958      2011      5      15
23/05/1981      2011      5      23
27/05/1964      2011      5      27
28/05/1964      2011      5      28
07/05/1959      2011      5      7
18/06/1965      2011      6      18
19/06/1968      2011      6      19
19/06/1969      2011      6      19
23/06/1961      2011      6      23
23/06/1966      2011      6      23
29/06/1954      2011      6      29
04/06/1971      2011      6      4
05/06/1967      2011      6      5
07/06/1963      2011      6      7
07/06/1973      2011      6      7
07/06/1964      2011      6      7
15/07/1975      2011      7      15
24/07/1976      2011      7      24
26/07/1966      2011      7      26
01/08/1969      2011      8      1
10/08/1959      2011      8      10
18/08/1946      2011      8      18
25/08/1956      2011      8      25
27/08/1953      2011      8      27
03/08/1981      2011      8      3
08/08/1948      2011      8      8
09/08/1961      2011      8      9
11/09/1968      2011      9      11
13/09/1951      2011      9      13
17/09/1952      2011      9      17
19/09/1973      2011      9      19
24/09/1968      2011      9      24
30/09/1981      2011      9      30

It doesn't seem to cause any strangeness.  I ran this on my Windows 7 box however that does get it right, I would probably have to install SQL Management studio on the XP client box to see if it was different on that. Bit of a problem as that is remote, but could be done if need be.

Siv

Changed my report and placed the code that produced the above into it instead of the actual query and it worked as per the picture:
AppRunning.jpg

The above post was run on the same XP box that previously gave the overflow error??

I was awaiting a more complete solution, but HainKurt gave me enough to move on so I think he should be allocated the points.
Siv

Although the solution did not completely answer my question, it gacve me enough to figure it out myself.