Conversion of DateTime

Question

I have the following query:

set @DataEnd = '
          select top 1 datetime 
          from IntradayHistory.dbo.IntradayDataHistory' + @product + ' 
          where TickerID = ' + cast(@ticker as nvarchar(24)) + ' 
          order by datetime desc
'
exec(@DateEnd)

This should return me only 1 value, and it did:

However, as I would need that value as a DateTime for dynamic query, how should I do it?

I have tried to add this in my CTE table: `where stime <= ''' + convert(nvarchar(max),@DateEnd,121) + '''` But to no avail too. — fauxpas, Aug 03 '17 at 08:24
Can you provide [sample data and expected output](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query)? Often it's easier to answer a question when we have some data to play with. — David Rushton, Aug 03 '17 at 08:30
@destination-data hi, I do not exactly have sample data but more so I would like to: `declare @DataEnd as datetime set @DataEnd = ' select top 1 datetime from IntradayHistory.dbo.IntradayDataHistory' + @product + ' where TickerID = ' + cast(@ticker as nvarchar(24)) + ' order by datetime desc'` reason is because I know that the query would give me only 1 datetime result. — fauxpas, Aug 03 '17 at 08:37
I think I understand. Are you trying to store `Date Time` in a var for use later on? If so take a peek at this [question](https://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable-for-sql-server). — David Rushton, Aug 03 '17 at 08:40
Hey @destination-data, you got it! However, I am still unclear on how to use the answer :/ Could you assist me? — fauxpas, Aug 03 '17 at 08:43

Nenad Zivkovic · Accepted Answer · 2017-08-03T10:05:08.123

2

Using sp_executesql you can send an output parameter to your dynamic query.

DECLARE @sqlCommand NVARCHAR(MAX);
DECLARE @dateEnd DATETIME;
SET @sqlCommand = '
          select top 1 @retVal = datetime 
          from IntradayHistory.dbo.IntradayDataHistory' + @product + ' 
          where TickerID = ' + cast(@ticker as nvarchar(24)) + ' 
          order by datetime desc
';
EXECUTE sp_executesql @sqlCommand, N'@retVal DATETIME OUTPUT', @retVal=@dateEnd OUTPUT;
SELECT @dateEnd;

Option B:

You can use temp table to store the result of dynamic query.

DECLARE @sqlCommand NVARCHAR(MAX);
SET @sqlCommand = '
      select top 1 datetime 
      from IntradayHistory.dbo.IntradayDataHistory' + @product + ' 
      where TickerID = ' + cast(@ticker as nvarchar(24)) + ' 
      order by datetime desc
';
CREATE TABLE #temp1 (Result DATETIME);
INSERT INTO #temp1 EXEC (@sqlCommand);
SELECT * FROM #temp1;

edited Aug 03 '17 at 10:05

answered Aug 03 '17 at 08:52

Nenad Zivkovic

18,221
6
42
55

Hi, I tried what you have shared, however, I got the error: `Msg 214, Level 16, State 2, Procedure sp_executesql, Line 21 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'` – fauxpas Aug 03 '17 at 08:57
@fauxpas you are missing one character there between `x` and `c` – Nenad Zivkovic Aug 03 '17 at 08:58
Sorry, I replaced the error, with the new one, noticed that too! – fauxpas Aug 03 '17 at 08:59
I got another error, after replacing the character: `Msg 214, Level 16, State 2, Procedure sp_executesql, Line 21 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'` and should the result I get be NULL? – fauxpas Aug 03 '17 at 09:00
have you used `NVARCHAR(MAX)` for sql statement? – Nenad Zivkovic Aug 03 '17 at 09:01

Conversion of DateTime

1 Answers1