Conversion failed from string to datetime in SQL Server in Dynamic Query

Question

I am facing the problem to convert into datetime..

DECLARE @sql nvarchar(max)
DECLARE @Lastdate datetime
SET @Update = N'    
                        SELECT TOP 1 
                            '+@Lastdate+' = Purchase.LastUpdated
                        FROM    Purchase
                        WHERE   ID = 15                         
                    '

LastUpdated is also datetime format in database

Then why It cannot convert

I want to set the datetime into declared variable @LastDate from purchase.lastupdate using sp_executesql only

@GordonLinoff I am trying to set the date to declared variable — Liam neesan
– Liam neesan, Commented Sep 20, 2018 at 11:44

Zhorov · Accepted Answer · 2018-09-20 12:33:41Z

2

This is another possible approach to set the value of @LastUpdated using sp_executesql:

-- Declaration
DECLARE @sql nvarchar(max)
DECLARE @LastUpdated datetime
DECLARE @err int

-- Statement
SET @sql = N'
    SELECT TOP 1 @LastUpdated = Purchase.LastUpdated
    FROM Purchase
    WHERE ID = 15
'
-- Execution
EXEC @err = sp_executesql 
    @sql,
    N'@LastUpdated datetime OUTPUT',
    @LastUpdated OUTPUT

-- Test output
IF @err = 0 
   PRINT @LastUpdated
ELSE 
   PRINT 'Error'

edited Sep 20, 2018 at 12:33

answered Sep 20, 2018 at 12:06

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-09-20 12:22:15Z

1

If you are trying to pass in a constant value, then use sp_executesql. Your query doesn't really seem very useful. This seems like a reasonable approximation:

DECLARE @LastUpdated datetime;
SET @Update = N'    
SELECT TOP 1 @LastUpdated as LastUpdated
FROM    Purchase
WHERE   ID = 15';

EXEC sp_executesql @Update,
                   N'@LastUpdated datetime',
                   @LastUpdated=@LastUpdated;

EDIT:

To update data, you need to use UPDATE. But you don't need dynamic SQL:

UPDATE Purchase
    SET LastUpdated = @LastUpdated
    WHERE ID = 15;

If ID is not unique and you want to limit this to one (arbitrary) row:

UPDATE p
    SET LastUpdated = @LastUpdated
    FROM (SELECT TOP (1)
          FROM Purchase
         ) p
    WHERE ID = 15;

You should use an ORDER BY with TOP so you can control which row you are affecting.

edited Sep 20, 2018 at 12:22

answered Sep 20, 2018 at 11:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Liam neesan Over a year ago

I want to set the value of Purchase.LastUpdated to @LastUpdated

Collectives™ on Stack Overflow

Conversion failed from string to datetime in SQL Server in Dynamic Query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related