Conversion failed when converting datetime from character string.

  • 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

  • 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]

    SQL-4-Life
  • 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

  • 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.

  • 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


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • 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

  • 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 :(.

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply