SQL Server Conversion failed when converting datetime from character string

Question

I have this query to fill in a date in a table that's giving me this error message:

Conversion failed when converting datetime from character string.

Below are my table declaration and query. What am I doing wrong and how can I fix it?

CREATE TABLE #inv
(
      Rep_LName NVARCHAR(50)
    , Rep_FName NVARCHAR(50)
    , Rep_ID NVARCHAR(50)
    , Rep_Email NVARCHAR(100)
    , Rep_Status NVARCHAR(50)
    , Rep_BU NVARCHAR(50)
    , Sales_Force NVARCHAR(50)
    , Territory NVARCHAR(50)
    , Sample_Eligibility NVARCHAR(50)
    , DM_Name NVARCHAR(100)
    , Phys_Inv_Date DATETIME
    , Last_Reconciled DATETIME
    , Inv_Type NVARCHAR(50)
    , Days_Since_Last_inv INT
)

I'm trying to fill the Phys_Inv_Date field inside a cursor like so:

OPEN Inventory_info

FETCH NEXT FROM Inventory_info INTO @rep_ID, @call_date

WHILE ( @@fetch_status = 0 ) BEGIN  

        SELECT
            @ls_Sql = 'update #inv set Phys_Inv_Date = case when inventory_type = ''physical'' then '
            + @call_date
            + ' else b.inv_date end from #inv a INNER JOIN (select top 1 call_date, rep_id from inv_header where call_date < '
            + @call_date + ' and rep_id = ''' + @rep_id
            + ''') b ON a.rep_id = b.rep_id WHERE Phys_Inv_Date IS NULL'

        EXEC (@ls_Sql)

        FETCH NEXT FROM Inventory_info INTO @rep_ID, @call_date

END

CLOSE Inventory_info
DEALLOCATE Inventory_info

What is the 'character string' you are trying to convert to a date? In other words, what is the value of @call_date? — Steve Wellens
– Steve Wellens, Commented May 9, 2013 at 19:48
Why are you using dynamic SQL here? It looks like you can use regular SQL with parameters, then you won't have to worry about formatting datetime values. — Pondlife
– Pondlife, Commented May 9, 2013 at 19:58

Pondlife · Accepted Answer · 2013-05-09 20:01:49Z

4

What's wrong with using SQL with parameters? There's no obvious reason why you need dynamic SQL here, and assuming that the variables are the correct data types then you won't need to convert anything:

update 
    #inv 
set 
    Phys_Inv_Date = case 
        when inventory_type = 'physical' then @call_date
        else b.inv_date end
 from 
    #inv a 
    INNER JOIN (
        select top 1 
            call_date, 
            rep_id 
        from 
            inv_header where call_date < @call_date
            and rep_id = @rep_id
    ) b
    ON a.rep_id = b.rep_id 
WHERE 
    Phys_Inv_Date IS NULL

answered May 9, 2013 at 20:01

Pondlife

16.3k6 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Javi Over a year ago

I ended up doing something similar to this. Thanks for your help!

RichardTheKiwi · Accepted Answer · 2013-05-09 19:52:41Z

Assuming call_date in inventory_info is a datetime column, you need to CONVERT it to a string

select @ls_Sql='
  update #inv set Phys_Inv_Date = case when inventory_type = ''physical''
                                  then ''' + CONVERT(varchar,@call_date,112) + '''
                                  else b.inv_date end
  from #inv a
  JOIN (select top 1 call_date, rep_id
        from inv_header
        where call_date < ''' + CONVERT(varchar,@call_date,112) + '''
        and rep_id = ''' + rtrim(@rep_id) + ''') b
    ON a.rep_id = b.rep_id
  WHERE Phys_Inv_Date IS NULL';

That gives you a string of the format YYYYMMDD, but you still need to enclose it in quotes, much the same as you've done with @rep_id. However, I'm guessing that @rep_id is actually an int, so you can use RTRIM to cast it to a varchar.

Note also that multi-line strings are allowed in SQL Server and make your code a lot more readable.

Collectives™ on Stack Overflow

SQL Server Conversion failed when converting datetime from character string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related