September 26, 2007 at 12:03 am
Hi,
I have written some code to get the next record in a list if the record above equals 0, but I get an 'Conversion failed when converting datetime from character string' error. I have tried using CAST and CONVERT to convert the datatype in the select statement to another format to no avail. I have also tried changing the datatypes in the variable declarations without success. I have listed the code below:
-- declare the variables to store the values returned by FETCH.
declare @wl_site varchar(64), @wl_waitlist varchar(48),
@wl_recordnum varchar(25), @wl_debtorcode varchar(16),
@wl_fname varchar(36), @wl_lname varchar(36),
@wl_realdate datetime, @wl_listeddate datetime,
@wl_assigneddate datetime, @wl_datechanged datetime,
@wl_status numeric(10,0)
-- declare the cursor.
declare waitlist_cursor cursor for
SELECTTOP (100) PERCENT Site_1.name, Waitlist_1.waitlist, Waitlist_1.RecordNum, Debtor4.code,
Debtor4.firstName, Debtor4.lastName, Waitlist_1.dtRealListed, Waitlist_1.dtListed,
Waitlist_1.dtAssigned, Waitlist_Log.dtChanged, Waitlist_Log.wStatus
FROMdbo.Waitlist AS Waitlist_1 INNER JOIN
dbo.SITE AS Site_1 ON Waitlist_1.SiteID = Site_1.lSiteID INNER JOIN
dbo.WLLOG AS Waitlist_Log ON Waitlist_1.RecordNum = Waitlist_Log.ridWaitlist INNER JOIN
dbo.DEBTOR4_CDP AS Debtor4 ON Waitlist_1.ridPatient = Debtor4.RecordNum
WHERE(Waitlist_1.waitlist = 'PROS') AND (Site_1.lSiteID = '19') AND Debtor4.lastName like '%Bauer%'
ORDER BYWaitlist_1.waitlist, Debtor4.code, Waitlist_1.RecordNum, Waitlist_Log.dtChanged
open waitlist_cursor
-- fetch the first record and store the values in variables.
fetch next from waitlist_cursor
into @wl_site, @wl_waitlist, @wl_recordnum, @wl_debtorcode, @wl_fname,
@wl_lname, @wl_realdate, @wl_listeddate, @wl_assigneddate,
@wl_datechanged, @wl_status
-- check to see if there are any more rows to fetch.
while @@FETCH_STATUS = 0
begin
if (@wl_status = 0)
begin
-- fetch the next record
fetch next from waitlist_cursor
into @wl_site, @wl_waitlist, @wl_recordnum, @wl_debtorcode, @wl_fname,
@wl_lname, @wl_realdate, @wl_listeddate, @wl_assigneddate,
@wl_datechanged, @wl_status
-- display the current values in the variables
print @wl_site + ', ' + @wl_waitlist + ', ' + @wl_recordnum + ', ' + @wl_debtorcode + ', ' + @wl_fname + ', ' +
@wl_lname + ', ' + @wl_realdate + ', ' + @wl_listeddate + ', ' + @wl_assigneddate + ', ' +
@wl_datechanged + ', ' + @wl_status
-- fetch the next record
fetch next from waitlist_cursor
into @wl_site, @wl_waitlist, @wl_recordnum, @wl_debtorcode, @wl_fname,
@wl_lname, @wl_realdate, @wl_listeddate, @wl_assigneddate,
@wl_datechanged, @wl_status
end
else
fetch next from waitlist_cursor
into @wl_site, @wl_waitlist, @wl_recordnum, @wl_debtorcode, @wl_fname,
@wl_lname, @wl_realdate, @wl_listeddate, @wl_assigneddate,
@wl_datechanged, @wl_status
end
close waitlist_cursor
deallocate waitlist_cursor
GO
Can someone please help me?
Thanking you in advance
Cherie
September 26, 2007 at 12:30 am
HI ,
Have you checked your DATA?
Sometimes the Data in your char/varchars is not compatiable with a datetime variable.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 26, 2007 at 12:50 am
Hi Chris,
The data is fine. All four date fields in the select statment from the table are datetime data types already, so it shouldn't be a problem. I am testing the code on a subset of the data and I have checked the subset and it appears to be fine. I have ran the code on the subset and still get the same error.
Cheers,
Cherie
September 26, 2007 at 1:12 am
Please post the definition at least of table Waitlist and table WLLOG (CREATE TABLE statements)... even better, of all tables used in this SQL. Maybe we will be able to find something then.
September 26, 2007 at 4:33 am
Try a SET DATEFORMAT statement at the top of your script, obviously using the correct settings for your data. If the dates were in the UK, I'd use
SET DATEFORMAT DMY
This can happen when the server is set for the default US English but the data is in another date format.
HTH
Dave J
September 26, 2007 at 4:52 am
You need to explicitly convert DATETIME variables to VARCHAR before you use it in concatenation for PRINT or anything else.
Otherwise Server is trying to convert all VARCHAR variables to dattime.
And of course it fails.
_____________
Code for TallyGenerator
September 26, 2007 at 5:31 am
Sergiy (9/26/2007)
You need to explicitly convert DATETIME variables to VARCHAR before you use it in concatenation for PRINT or anything else.Otherwise Server is trying to convert all VARCHAR variables to dattime.
And of course it fails.
Oh, sure, that's it... my bad, I checked everything except the PRINT statement :(.
September 26, 2007 at 5:47 am
Cherie_Bonnici,
Sergiy is correct. You will have to explicitly convert DateTime to Varchar when concatenating values in a PRINT statement. For more details regarding "CAST/CONVERT' see the following link: http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx
Quick Example:
declare @date datetime
declare @string varchar(8000)
set @string = 'This is a test.'
set @date = getdate()
print @date
-- Not the above statement will work, no concatenation
print @string + ' --- ' + convert(varchar(8000),@date) + ' --- ' + @string
print @string + ' --- ' + convert(varchar(12),@date) + ' --- ' + @string
print @string + ' --- ' + convert(varchar,@date) + ' --- ' + @string
print @string + ' --- ' + convert(varchar,@date,9) + ' --- ' + @string
print @string + ' --- ' + convert(varchar,@date,13) + ' --- ' + @string
print @string + ' --- ' + convert(varchar,@date,20) + ' --- ' + @string
Regards,
Wameng Vang
MCTS
September 26, 2007 at 5:15 pm
Hi All,
I ran the following two statements within the main code as tests and got the following error for both:
Msg 8115, Level 16, State 6, Line 45
Arithmetic overflow error converting varchar to data type numeric.
Test 1:
print @wl_site + ', ' + @wl_waitlist + ', ' + @wl_recordnum + ', ' + @wl_debtorcode + ', ' + @wl_fname + ', ' + @wl_lname + ', ' + convert(varchar,@wl_realdate,20) + ', ' + convert(varchar,@wl_listeddate,20) + ', ' + convert(varchar,@wl_assigneddate,20) + ', ' + convert(varchar,@wl_dtChanged,20) + ', ' + @wl_status
Test 2:
print @wl_site + ', ' + @wl_waitlist + ', ' + @wl_recordnum + ', ' + @wl_debtorcode + ', ' + @wl_fname + ', ' + @wl_lname + ', ' + convert(varchar(38),@wl_realdate) + ', ' + convert(varchar(38),@wl_listeddate) + ', ' + convert(varchar(38),@wl_assigneddate) + ', ' + convert(varchar(38),@wl_dtChanged) + ', ' + @wl_status
Thanks,
Cherie
September 26, 2007 at 6:27 pm
A little bit of thinking could bring you to a conclusion that you probably need to do this conversion not only for dates...
_____________
Code for TallyGenerator
September 26, 2007 at 7:57 pm
Msg 8115, Level 16, State 6, Line 45
Arithmetic overflow error converting varchar to data type numeric.
I think the problem in that error is not about the datetime but converting from varchar to numeric data type. If you want to convert datetime to varchar / char data type you can do this:
CONVERT(char(8),getdate(),112)
-- this will convert getdate() to a char with 8 characters
--your date will be 20070927 ( if date is September 27, 2007) try to play with the last number '112' change it with 113,110 etc depends on what format you prefer
"-=Still Learning=-"
Lester Policarpio
September 26, 2007 at 8:07 pm
Ya this can be a pain to do. What I usually do is comment out 50% of the column and try the query. If I get no error then I know that the error is in the other 50% (binary search logic). The problem in that process is that the error can be caused in more than 1 column, so you eventually have to try them all. What I usually do is binary search logic untill I find a problem. After I fix the problem, I run again with all the columns. Repeat untill the query works. This can be a real pain if the query takes a long time to run, I hope you have a powerfull dev server to run those tests.
September 26, 2007 at 9:39 pm
Lester, as I wrote that that must be done NOT ONLY FOR DATES.
Same method must be used for all non-string fields.
It used to be "convert to datetime" error.
Now, when datetimes converted to varchar it's "convert to numeric" error.
Just next variable is affected when dates are fixed.
_____________
Code for TallyGenerator
September 26, 2007 at 10:04 pm
Thanks for all the help.
I figured out that the second error was caused by the declaration of the @wl_status variable. Once I fixed this and then included the date conversions in the print statement the query worked.
To figure this out, I removed the all the date fields and then ran the query again. That when I found that I was still getting the "converting to numeric" error. The only variable with the type numeric was the @wl_status variable. I changed this type to varchar and then ran the query with a successful result. Then I included one of the date variables with the date conversion in the print statement also with a successful result.
All I needed to do after that was include the rest of the date variables and date conversions. It worked perfectly.
Thanks,
Cherie
September 26, 2007 at 10:19 pm
Cherie,
Just a tip in case you didn't know... if you get such an error in Query Analyzer (message window), the first thing you should probably do is to double click on the error... about half the time, it'll take you right to the offending line. Most of the other time, it'll at least take you to the offending section (query) of the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply