Select Top 1 field and assign to local variable
SqlSql ServerSql Problem Overview
I want to take the value of ExtractedDate from this query and use it as @LastExtractDate in the next query. How do I do that?
SELECT TOP 1 [ExtractedDate]
FROM [OnsiteV4].[dbo].[SqlPendingIndex] order by ExtractedDate desc
next query:
insert into @table(Hex, KeyDeviceId, ObjectDateTime, ExtractedDate )
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ObjectValue, 1)) AS Hex, KeyDeviceId, ObjectDateTime , GETDATE ()
FROM SQLPending
WHERE (ObjectSubType LIKE '%GAS%') and (ObjectDateTime > @LastExtractDate)
Sql Solutions
Solution 1 - Sql
why not use this:
declare @LastExtractDate date
SELECT TOP 1 @LastExtractDate=[ExtractedDate]
FROM [OnsiteV4].[dbo].[SqlPendingIndex] order by ExtractedDate desc
Solution 2 - Sql
Simply declare & assign:
DECLARE @LastExtractDate DATETIME = (
SELECT TOP 1 [ExtractedDate] FROM [OnsiteV4].[dbo].[SqlPendingIndex] order by ExtractedDate desc
)
or better:
DECLARE @LastExtractDate DATETIME = (
SELECT MAX(ExtractedDate) FROM [OnsiteV4].[dbo].[SqlPendingIndex]
)
Solution 3 - Sql
Use this:
DECLARE @ExtractedDate DATETIME
SET @ExtractedDate = (SELECT TOP 1 ExtractedDate
FROM [OnsiteV4].[dbo].[SqlPendingIndex]
ORDER BY ExtractedDate DESC
Solution 4 - Sql
Try this simply
declare @LastExtractDate date
SELECT @LastExtractDate=MAX([ExtractedDate])
FROM [OnsiteV4].[dbo].[SqlPendingIndex]