Dynamically Convert Date/time from string Error

Question

Dynamically Convert Date/time from string Error

Priya004

Ten Centuries

Points: 1368
More actions
November 13, 2014 at 7:36 pm

#292557

The below code is throwing error
Conversion failed when converting date and/or time from character string.
DECLARE @QUERY VARCHAR(MAX)
DECLARE @STARTTIME DATETIME
DECLARE @ProdDBName_testlog VARCHAR(30)
DECLARE @ProdTableName_testlog VARCHAR(30)
SET @ProdDBName_testlog = master
SET @ProdTableName_testlog = 'employee'
SET @QUERY = N'SELECT ' +@STARTTIME+'= STARTTIME FROM ' +@ProdDBName_testlog+'.dbo.'+@ProdTableName_testlog
EXEC (@QUERY)
DATETIME format:'2014-07-01 00:03:43.000'
Even I write Convert (varchar(30),startime) in the query it is throwing error.Plz help

Viewing 3 posts - 1 through 2 (of 2 total)

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

To me, it looks like you are trying to get a value from a log table and assign it to variable @STARTTIME. When you use the EXEC command, all of your variables are not visible to the command you are executing.

One way to work around this is to create a temporary table and insert you start time into that. Temporary tables are scoped so that they are visible to the EXEC command.

IF I am right, something like the following should work for you....

DECLARE @QUERY VARCHAR(MAX)

DECLARE @STARTTIME DATETIME

DECLARE @ProdDBName_testlog VARCHAR(30)

DECLARE @ProdTableName_testlog VARCHAR(30)

SET @ProdDBName_testlog = master

SET @ProdTableName_testlog = 'employee'

CREATE TABLE #DateTable (StartTime DATETIME)

SET @QUERY = N'INSERT INTO #DateTable SELECT STARTTIME FROM ' +@ProdDBName_testlog+'.dbo.'+@ProdTableName_testlog

EXEC (@QUERY)

SELECT @STARTTIME = StartTime FROM #DateTable

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You don't need to use an additional table. You just need to use the dynamic sql correctly.

DECLARE @QUERY NVARCHAR(4000)

DECLARE @STARTTIME DATETIME

DECLARE @ProdDBName_testlog VARCHAR(30)

DECLARE @ProdTableName_testlog VARCHAR(30)

SET @ProdDBName_testlog = 'master'

SET @ProdTableName_testlog = 'employee'

SET @QUERY = N'SELECT @STARTTIME = STARTTIME FROM ' + QUOTENAME(@ProdDBName_testlog)+'.dbo.'+QUOTENAME(@ProdTableName_testlog)

EXEC sp_executesql @QUERY, N'@STARTTIME DATETIME OUTPUT', @STARTTIME OUTPUT

SELECT @STARTTIME

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2