Help with Date Conversion error

[bloko]

Someone wrote the below code to run in SQL server 2005. For some strange reason after months of running fine it comes up with the error

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value"

The sql code is needed to run a crystal report, which comes up with the same error. I have looked at this for a couple of days with no results so any help would be appreciated.

SELECT TOP (100) PERCENT
CAST(cal.period AS int) AS period,
CAST(SUM(CONVERT(decimal(12, 2), CASE WHEN CONVERT(smalldatetime, invo.[grn posted],
103) <= CONVERT(smalldatetime, invo.[statdeldte], 103) THEN 1 ELSE 0 END)) / CAST(COUNT(*) AS int) * 100 AS decimal(12, 2)) AS Actual,
95 AS Target
FROM dbo.ZSPO AS invo INNER JOIN
dbo.Period_cal AS cal ON CONVERT(smalldatetime, invo.[GRN Posted], 103) = CONVERT(smalldatetime, cal.Date, 103)
WHERE (invo.Material <> '') AND (CAST(REPLACE(invo.[GRN Qty], ',', '') AS int) > 0)
GROUP BY cal.period
HAVING (cal.period >=
(SELECT period
FROM dbo.Period_cal
WHERE (Date = CONVERT(char(10), DATEADD(dd, - 61, GETDATE()), 103))))

 

[Jamfool]

wow, thats some nasty code.

assuming all the comparisons are correct, and it is structured the way it should be ( bit hard to read ;p ), it will probably be some rogue data.



try the following and see if it errors, at least it will guide you.

SELECT 
CONVERT(smalldatetime, invo.[grn posted], 103),    
CONVERT(smalldatetime, invo.[statdeldte], 103),  
CONVERT(smalldatetime, invo.[grn posted], 103),  
CONVERT(smalldatetime, invo.[grn posted], 103) 
FROM dbo.ZSPO AS invo
                                           
SELECT 
CONVERT(smalldatetime, cal.Date, 103)              
FROM dbo.Period_cal AS cal

 

[bborissov]

SmallDateTime can have values from:
01 Jan 1900 to 06 Jun 2079
So check your table and see if you are not out of range.
If you are just use DateTime, not SamllDateTime.

BTW why you need CONVERT?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bloko]

Hi Jamfool,

I ran your code and it did run but threw up an error. Once i okayed it, it ran, I had a look through the data and it seems ok. The error message came up on record 18432, below is a section of the data. As for being nasty code, i can see why the guy left who wrote it as I am having to de and re code daily and im no expert.

18431 02/03/2010 00:00:00 12/03/2010 00:00:00 02/03/2010 00:00:00 02/03/2010 00:00:00
18432 23/02/2010 00:00:00 12/03/2010 00:00:00 23/02/2010 00:00:00 23/02/2010 00:00:00
18433 02/03/2010 00:00:00 12/03/2010 00:00:00 02/03/2010 00:00:00 02/03/2010 00:00:00

Hi bborissov,

I have no idea why convert is being used, I assume it to match up with the same data type as the table its getting the data from, but thats a guess.

I did manage to get it to give me some data by messing with the date clause. The below code gave me 1 months worth of data. I will look at the table where the data comes from to see if some rogue data is causing the issue.

WHERE (Date = CONVERT(char(10), DATEADD(dd, - 1, GETDATE()), 103))))

 

[Jamfool]

copy and paste getting the better of me there in my last query. couple of duplicate lines ;p

double check that there are only valid dates in your table, even tho they may look valid:

SELECT 
ISDATE(invo.[grn posted]) ,
invo.[grn posted],
ISDATE(invo.[statdeldte]) 
invo.[statdeldte],
FROM dbo.ZSPO AS invo
WHERE ISDATE(invo.[grn posted]) = 0 or ISDATE(invo.[statdeldte]) = 0

Example Problems:

a carriage return for example can cause a problem in a string:

SELECT CONVERT(smalldatetime, '23/02/2010 00:00:00
', 103)

as bborissov said the type you are using is constrained to what dates it can store:

SELECT CONVERT(smalldatetime, '23/02/2080 00:00:00', 103)

 

[gmmastros]

I would suggest reading this: faq183-6419

There are code samples in there. You should copy/paste the code samples to a query window and run them. This will give you a better understanding of the SmallDateTime issues you apparently are experiencing.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bloko]

Hi Jamfool

there are over 2000 rows, so will get searching and hope I find it near the top.

I ran your query

SELECT ISDATE([GRN Posted]) AS Expr1, [GRN Posted], ISDATE(StatDelDte) AS Expr2
FROM dbo.ZSPO AS invo
WHERE (ISDATE([GRN Posted]) = 0) OR
(ISDATE(StatDelDte) = 0)

and got

1 20080704 0
1 20080704 0

Thanks george for the link. I will look at this if I cant find any rogue data.

 

[gmmastros]

bloko,

You should read the link before looking for bad data. In the FAQ's I pointed you to, there is a user defined function that will help you find your bad data. Specifically, there is an IsDate function that will determine if a string CAN be converted to DateTime. But, since SmallDateTime has a narrower range of dates, some times a string will be a valid DateTime but an invalid SmallDateTime. The link I pointed you to contains a user defined function that you can add to your database, and then use to find those strings that cannot be converted to SmallDateTime.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jamfool]

it looks like you have at least 2 rows of bad dates, it looks like we dont have the bits we need tho as we should have four columns per row.

need to change it too

ISDATE([GRN Posted]) AS Expr1,
[GRN Posted],
ISDATE(StatDelDte) AS Expr2
[StatDelDte],

as it looks from the 0 that in the 3rd column that StatDelDte is not a valid date. so try rerunning the query.



if there are still problems once we have eliminated the bad dates, then it would be a good idea to review the date ranges as bborissov said. (& george has provided a helpful link.)

 

[bloko]

I found 2 records which have blank statdeldte. Can blank date be causing the issue.

p.s

Thanks to everyone who is helping me, it is appreciated.

 

[gmmastros]

A blank date will convert as Jan 1, 1900.

Select Convert(SmallDateTime, '')

1900-01-01 00:00:00



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jamfool]

no blank dates wont cause the issue.

do you still get an error if you run:

SELECT 
CONVERT(datetime, invo.[grn posted], 103),    
CONVERT(datetime, invo.[statdeldte], 103)
FROM dbo.ZSPO AS invo

 

[bloko]

Jamfool - No I dont get an error

gmmastros - Thanks for the info

 

[bloko]

Hi Guys,

Still cant find the problem. I did find that instead of putting -1 in the date add part I can put -3 and it shows this months data. If i put -4, it errors, so I think it could be somthing to do with the days or months of the date.

WHERE (Date = CONVERT(char(10), DATEADD(dd, - 3, GETDATE()), 103))))