February 5, 2014 at 12:51 pm
I have the following table ( its an import from excel spreadsheet done through an application) I know the datatypes are horrible
CREATE TABLE [dbo].[_import_2_1_AppointmentsWorking](
[AutoTempID] [int] IDENTITY(1,1) NOT NULL,
[patientaccountnumber] [varchar](max) NULL,
[resourcefirstname] [varchar](max) NULL,
[resourcelastname] [varchar](max) NULL,
[appointmentcode] [varchar](max) NULL,
[reason] [varchar](max) NULL,
[startdate] [varchar](max) NULL,
[starttm] [varchar](max) NULL,
[enddate] [varchar](max) NULL,
[endtm] [varchar](max) NULL,
[duration] [varchar](max) NULL,
[locationdescription] [varchar](max) NULL,
[appointmentnotes] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
When I run the following query:
SELECT DISTINCT
pat.PatientID ,
1 ,
sl.ServiceLocationID ,
DATEADD(hh , -2 ,app.startdate) ,
DATEADD(hh , -2 ,app.enddate) ,
'P' ,
'' ,
app.appointmentnotes ,
GETDATE() ,
0 ,
GETDATE() ,
0 ,
1 ,
'S' ,
pc.PatientCaseID ,
dk.DKPracticeID ,
dk.DKPracticeID ,
app.starttm -200 ,
[highlight=#ffff11]app.endtm -200 [/highlight]
FROM dbo.[_import_2_1_AppointmentsWorking] AS app
INNER JOIN dbo.Patient AS pat ON
pat.VendorID = app.patientaccountnumber AND
pat.PracticeID = 1
INNER JOIN dbo.PatientCase AS pc ON
pc.VendorID = app.patientaccountnumber AND
pc.PracticeID = 1
INNER JOIN dbo.ServiceLocation AS sl ON
sl.Name = app.locationdescription AND
sl.PracticeID = 1
INNER JOIN dbo.DateKeyToPractice dk ON
dk.PracticeID = 1 AND
dk.Dt = (SELECT Dt FROM dbo.DateKeyToPractice WHERE Dt = CAST(CAST(app.startdate AS DATE) AS DATETIME))
I get the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'NaN' to data type int.
I determined the error is coming from the highlighted spot above. All the data is numbers. And if I select the data without trying to do the calculation into a temp table and then do the calculation the implicit conversion works fine.
Trying to figure out why its failing. Also the data types in the temp table also are the same
Here is some sample data from the table I defined above
AutoTempID,patientaccountnumber,resourcefirstname,resourcelastname,appointmentcode,reason,startdate,starttm,enddate,endtm,duration,locationdescription,appointmentnotes
1,66244,JANE,WALKER,NP,New Patient/LOOSING HAIR,10/14/2013 13:45,1345,10/14/2013 14:00,1400,15,Dr. Smith's Office,
2,82019,JANE,WALKER,5,5 Min,2/11/2014 14:00,1400,2/11/2014 14:05,1405,5,Dr. Smith's Office,
3,84059,JANE,WALKER,5,5 Min,10/30/2013 10:15,1015,10/30/2013 10:20,1020,5,Dr. Smith's Office,
4,59334,JANE,WALKER,5,5 Min,10/30/2013 10:15,1015,10/30/2013 10:20,1020,5,Dr. Smith's Office,
5,57021,JANE,WALKER,5,5 Min,2/3/2014 13:15,1315,2/3/2014 13:20,1320,5,Dr. Smith's Office,
6,84199,JANE,WALKER,5,5 Min,3/3/2014 16:00,1600,3/3/2014 16:05,1605,5,Dr. Smith's Office,
7,57226,JANE,WALKER,NP,New Patient,12/16/2013 15:00,1500,12/16/2013 15:15,1515,15,Dr. Smith's Office,
8,50222,JANE,WALKER,5,5 Min,2/11/2014 14:00,1400,2/11/2014 14:05,1405,5,Dr. Smith's Office,
9,58868,JANE,WALKER,5,5 Min,2/3/2014 13:15,1315,2/3/2014 13:20,1320,5,Dr. Smith's Office,
10,19598,JANE,WALKER,5,C&D,10/16/2013 8:45,845,10/16/2013 8:50,850,5,Dr. Smith's Office,
February 5, 2014 at 1:02 pm
Can you post your sample data as insert statements?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 2:05 pm
Perhaps the apostrophes in the data is monkeying with something? And is it the datetime field -200 that is causing the integer error?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2014 at 2:15 pm
At a guess, check your app.startdate field. Also check the join fields from the source, see if they're trying to convert incorrectly to int.
You're going to have to dig through this in inches. First, SELECT * FROM ... all joins. Error still? Cut out half the joins... no error, include the WHERE clause... etc.
Once you find the exact line, you'll know what field to go dig into.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 5, 2014 at 2:34 pm
I know the field. Its the endtm field. It is all numbers even though its being stored as varchar. The weird part is if gather all the data out of the table and put into a #temp table the endtm-200 works fine.
February 5, 2014 at 2:41 pm
pamozer (2/5/2014)
I know the field. Its the endtm field. It is all numbers even though its being stored as varchar. The weird part is if gather all the data out of the table and put into a #temp table the endtm-200 works fine.
OK so cut out all the noise from the other tables.
What happens if you run this query?
select app.endtm -200
FROM dbo.[_import_2_1_AppointmentsWorking] AS app
Maybe it is your implicit conversion doing something somewhat unexpected.
select cast(app.endtm as int) -200
FROM dbo.[_import_2_1_AppointmentsWorking] AS app
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 3:02 pm
Yeah that would have made more sense just to do the select from the table. I apologize for the confusion. Conversion failed when converting the varchar value 'NaN' to data type int. Implicitly or cast/convert function. Here is the insert script for the data
INSERT INTO [dbo].[_import_2_1_AppointmentsWorking]
([patientaccountnumber]
,[resourcefirstname]
,[resourcelastname]
,[appointmentcode]
,[reason]
,[startdate]
,[starttm]
,[enddate]
,[endtm]
,[duration]
,[locationdescription]
) values('66244','STEPHANIE','SMITH','NP','New Patient/LOOSING HAIR','10/14/2013 13:45','1345','10/14/2013 14:00','1400','15','DERMATOLOGY ')
,('82019','STEPHANIE','SMITH','5','5 Min','02/11/2014 14:00','1400','02/11/2014 14:05','1405','5','DERMATOLOGY ')
,('84059','STEPHANIE','SMITH','5','5 Min','10/30/2013 10:15','1015','10/30/2013 10:20','1020','5','DERMATOLOGY ')
,('59334','STEPHANIE','SMITH','5','5 Min','10/30/2013 10:15','1015','10/30/2013 10:20','1020','5','DERMATOLOGY ')
,('57021','STEPHANIE','SMITH','5','5 Min','02/03/2014 13:15','1315','02/03/2014 13:20','1320','5','DERMATOLOGY ')
,('84199','STEPHANIE','SMITH','5','5 Min','03/03/2014 16:00','1600','03/03/2014 16:05','1605','5','DERMATOLOGY ')
,('57226','STEPHANIE','SMITH','NP','New Patient','12/16/2013 15:00','1500','12/16/2013 15:15','1515','15','DERMATOLOGY ')
,('50222','STEPHANIE','SMITH','5','5 Min','02/11/2014 14:00','1400','02/11/2014 14:05','1405','5','DERMATOLOGY ')
,('58868','STEPHANIE','SMITH','5','5 Min','02/03/2014 13:15','1315','02/03/2014 13:20','1320','5','DERMATOLOGY ')
,('19598','STEPHANIE','SMITH','5','C&D','10/16/2013 08:45','845','10/16/2013 08:50','0850','5','DERMATOLOGY ')
February 5, 2014 at 3:47 pm
You must have something funky in your data. The insert you posted works perfectly fine with either query.
What about this one?
select app.endtm
FROM dbo.[_import_2_1_AppointmentsWorking] AS app
where isnumeric(app.endtm) = 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 3:51 pm
thanks. I found the funky data. I hate when its something so stupid. I appreciate your help and time.
February 5, 2014 at 3:53 pm
pamozer (2/5/2014)
thanks. I found the funky data. I hate when its something so stupid. I appreciate your help and time.
No problem. I going to guess that you found at least one row with 'NaN' as the value?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 4:01 pm
Exactly.
February 5, 2014 at 4:29 pm
Depending on what you need, you might want to take a look at TRY_CAST() or TRY_CONVERT()
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply