February 12, 2009 at 8:50 am
I am working on a long query that someone else wrote as a command for a Crystal Report. This command works correctly when filtered on other practices, but produces the following error with one practice. I assume that there is a bad date in one of the tables, but what is the easiest way to find that error? (Below: Both line 1 and line 260 are the beginning of long select statements)
Error msg in SQL Management console:
Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 296, Level 16, State 3, Line 260
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.
thanks
michele
February 12, 2009 at 9:05 am
The easy way to find out is if the char you are sending comes with seconds because SmallDateTime cannot store seconds so you may have to change SmallDateTime to DateTime. Check below for the SQL Server DateTime guide.
http://www.karaszi.com/SQLServer/info_datetime.asp
Kind regards,
Gift Peddie
February 12, 2009 at 9:06 am
Have you tried changing to datetime instead of smalldatetime? Out-of-range errors on smalldatetime can often be handled that way.
If that doesn't do it, I'd start with analyzing the data source by running the query in management studio and seeing what it takes to clean it up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 12, 2009 at 9:31 am
I replaced all the smalldatetime conversions with datetime and now I get the following error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.
I am running the query in the SQL Management Studio, but am unsure what to do next to find which of the dozen or more tables in the query has the value that is causing the error.
thanks in advance
michele
February 12, 2009 at 9:40 am
The SET options are based of DateTime format and it is covered in the link I posted.
All aggregates ignore nulls except COUNT(*) so you must use it with aggregate functions. Check below for details.
http://qa.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/
Kind regards,
Gift Peddie
February 12, 2009 at 11:37 am
Are the line numbers that SQL puts in the error accurate? I am trying to compare the code (the select clause that begins) in lines 1 and 260 of the command. I am looking in the date fields, but not finding the data that is causing the error. As I said, the command works for 4 of the practices, but not one. The clauses are the following:
SELECT status.practice_id, status.person_id, status.diabetes_flag, status.Status, person.dob,
person.last_name, person.first_name, dates.visit, dates.EyeExam, dates.footexam, assign.assigned_pcp, CONVERT(smalldatetime, sm.date) AS smdate,
ss.status AS smokingstatus, CONVERT(smalldatetime, lastldl.ldldate) AS ldldate, lastldl.ldlvalue AS ldlvalue, a1cs.hga1cdate, a1cs.hgA1cPercent,
CASE WHEN a1csRisk.hgA1cPercent >= 7 THEN 'High Risk' WHEN DateDiff(month, CONVERT(smalldatetime, a1csRisk.hga1cdate), GetDate()) > 5 THEN 'Medium Risk'
WHEN a1csRisk.hga1cdate is null or a1csRisk.hgA1cPercent is null THEN 'No HGBA1c' ELSE 'Low Risk' END AS Risk, bp.bp_systolic,
bp.bp_diastolic
____________
SELECT assign.assigned_pcp, status.practice_id, status.person_id, status.diabetes_flag, status.Status, person.dob,
person.last_name, person.first_name, LastA1c.hga1cpercent,
case when len(LastA1c.hga1cpercent)= 4 Then cast(cast(LastA1c.hga1cpercent as varchar) as decimal(3,1))
when len(LastA1c.hga1cpercent) < 4 Then cast(cast(LastA1c.hga1cpercent as varchar) as decimal(2,1)) end as hga1ccomp,
LastA1c.hga1cdate,
Case when DateDiff(day, LastA1c.hga1cdate, getdate()) < = 365 Then 1 else 0 end as OneA1c,
TwoA1cCount.TwoA1cCount,
Case when LastA1c.hga1cpercent > 9 then 1 Else 0 End as A1cPoorControl,
Case When LastA1c.hga1cpercent < 7 then 1 Else 0 End as A1cGoodControl,
Case when datediff(day, lastLDL.ldlDate,getdate())<=365 then 1 else 0 end as LDLCount, LastLDL.ldlvalue,
Case when LastLDL.ldlValue >=130 and datediff(day, lastldl.ldldate,getdate())<=365 then 1 else 0 end as LDLPoorControl,
Case when lastldl.ldlvalue <100 and datediff(day, lastldl.ldldate,getdate())<=365 then 1 else 0 end as LDLGoodControl,
Case when datediff(day, dates.eyeexam, getdate())<=365 then 1 Else 0 End as EyeExamCount,
Case When datediff(day, dates.FootExam, getdate()) <=365 then 1 Else 0 End as FootExamCount,
Case When datediff(day, sm.date, getdate()) < =365 then 1 Else 0 End as SMCount,
Case When ss.status = 'Current' Then 1 Else 0 End as SSCount,
Case When ss.status = 'Current' and datediff(day, ssc.date, getdate()) < = 365 then 1 Else 0 End as SSCCount,
Case When bp.bp_systolic is not null and bp.bp_diastolic is not null then 1 else 0 End as BPCount,
Case When bp.bp_systolic < 140 and bp.bp_diastolic< 90 then 1 Else 0 End as BP140Count,
Case When bp.bp_systolic < 130 and bp.bp_diastolic < 80 then 1 Else 0 End as BP130Count,
Case When datediff(day, macro.date, getdate()) <= 365 then 1 Else 0 End as MacroCount,
Case When datediff(day, macro.date, getdate()) <= 365 and datediff(day, micro.date, getdate()) <= 365 then 1 Else 0 End as MicroCount
I need to find the offending data as soon as possible. Any help is appreciated. thanks
michele
February 12, 2009 at 11:48 am
person.last_name, person.first_name, dates.visit, dates.EyeExam, dates.footexam, assign.assigned_pcp, CONVERT(smalldatetime, sm.date) AS smdate,
I on the other hand cannot understand why someone can take three Date columns which I am assuming is defined as Char and converting it to SmallDateTime and yes SQL Server is right it is more than DateTime in bytes. So you need to take this code apart get your columns and do manual design before writing another line of code.
Kind regards,
Gift Peddie
February 12, 2009 at 11:57 am
I am not actually writing any code - I am taking apart someone else's code. Can you tell me if the line numbers are generally accurate in the error messages?
michele
February 12, 2009 at 12:03 pm
That code I quoted is passing 24 bytes in Char to 4 bytes that is what SmallDateTime is defined. So convert all the Date columns to DateTime, then you will know how to handle your Date calulations.
So go into your tables and see what is stored and you will see what you need to change, SQL Server is counting bytes so you have to do the counting also.
Kind regards,
Gift Peddie
February 12, 2009 at 12:20 pm
When I check the data in the table for the practice that fails - the dates all have length 8. The practices that succeed also have length 8. Is this what you mean by size?
michele
February 12, 2009 at 12:24 pm
Right click on the tables and script all out the create statements you will see there is a difference.
I just remembered if there is no difference in Data types then it is related to the Date format because that may be the reason for the SET error.
Kind regards,
Gift Peddie
February 12, 2009 at 12:43 pm
Check out the IsDate() function in BOL.
I have had this issue in a data import where data was incorrectly entered like '12-31-0007' which is an invalid smalldatetime and datetime. Also if you are working with birth dates and you have any historical data you may have a 19th century birth date which falls outside the smalldatetime range.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 12, 2009 at 4:42 pm
Thank you all for your help!
I found an invalid date of 20080900 using the IsDate() function. The application is not supposed to allow this type of entry, of course, but we should be able to correct the data and run the report command now.
michele
February 12, 2009 at 4:46 pm
These are the reasons you need to use only DateTime Data type for Date so you don't get invalid data. The other option is to use Validators that will reject invalid information.
Kind regards,
Gift Peddie
February 16, 2009 at 9:17 am
grasshopper
Don't we just hate dates? Anyway this is not in reply to your specific question but a datetime handler in itself. I too had problems attempting to put a date that looked like 2008.01.12 into an existing third party software whose format looked like 01/12/2008. So I was looking to put yyyy.mm.dd into mm/dd/yyyy. Alas this failed with the "failure to convert etc." Trial and error lead to this:-
[select .....
"DoB" = case when DoB is not null then
cast(convert(char(12),replace(StudentDoB,'-','/'),126)as datetime) Else 1/1/1901 end
from ....etc.]
It is the combination of replacing . and / or whatever, converting it to char and then casting it out as datetime. It may be a start
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply