Reference Excel for date?

I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME)) AND (DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112)
AS DATETIME)) AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

what I would like to do is reference 2 cells in Excel with dates if I need to run this manually. How would I change the sql to do this. Thanks

DateTime1 = cell(1,1)

DateTime2 = cell(2,1)

Something like this:

Code Snippet

declare @.DateTime1 datetime,

@.DateTime2 datetime

select @.DateTime1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

select @.DateTime2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time

FROM v_AnalogHistory

WHERE (@.DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME))

AND (@.DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112) AS DATETIME))

AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta')

AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)

ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

You'll need to ensure that 'Ad Hoc Remote Queries' is enabled on your instance to allow OPENROWSET to function.

|||

Expanding upon Dale's suggestion, if you want to be able to run the code as it is, or on occasion, manually, then it would be better to create a Stored Procedure and call the Stored Procedure. (NOT tested and not asserted to be accurate or runnable -presented for ideas only.)

This procedure can be called in this fashion:

EXECUTE PROCEDURE dbo.MyProcedure

In this case it will attempt to find date values in the Excel File and use them.

If the cells are empty, then the procedure will use the columns DateTime1 and DateTime2.

(From the original posting, I'm assuming that there are such columns...)

EXECUTE PROCEDURE dbo.MyProcedure '2007/06/01', '2007/07/01'

In this case, the procedure will use the dates supplied.

Code Snippet

CREATE PROCEDURE dbo.MyProcedure

( @.InDate1 datetime = NULL,

@.InDate2 datetime = NULL

)

AS

BEGIN

DECLARE

@.ExDate1 datetime,

@.ExDate2 datetime

SELECT @.ExDate1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

SELECT @.ExDate2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1

DateTime,

TagName,

[Value],

Time = convert( varchar(15), DateTime, 108 )

FROM v_AnalogHistory

WHERE ( coalesce( InDate1, nullif( ExDate1, '' ), DateTime1 ) >=

cast( convert( varchar(8), getdate() -4, 112) AS datetime )

AND coalesce( InDate2, nullif( ExDate2, '' ), DateTime2 ) <

cast( convert( varchar(8), getdate() -3, 112) AS datetime )

AND TagName = N'AIT-500_TOTAL_NH2CL'

AND wwRetrievalMode = N'delta'

AND convert( decimal(38,3), [Value] ) IS NOT NULL

)

ORDER BY

convert( decimal(38,3), [Value] ),

convert( varchar(15), DateTime, 108 )

END

GO