I have a database that has a vessel pressure that is trended every 30 seconds. I would like to be able to retrieve a recording at a specific time of day for a number of days in the past. I am not able to base this on the server as a Stored Procedure or a View – I only have read access. I have created a routine that works for 17 days in the past but I would like it to be able to give a few months data. I could I guess just make repeated calls from the c# app, but that seems even more inefficient than the method I am using now. Does anyone have any recommendations.

declare @DeadBand DateTime
declare @EndDate DateTime
declare @End varchar(50)
declare @Start varchar(50)
declare @Temp varchar(50)
declare @NumberDays int
declare @dec int

declare @InnerSqlQry as varchar(8000)
declare @SqlQry as varchar(8000)
SET @NumberDays = 5
SET @dec = @NumberDays
SET @Start = CONVERT(Varchar(30),dateadd(dd,-@NumberDays,GetDate()))
SET @End = CONVERT(Varchar(30),GetDate())
SET @DeadBand= + CONVERT(Varchar(30),dateadd(ss,90,@Start))

   SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))
WHILE (@dec >= 1)
    BEGIN

                set @InnerSqlQry = ' Select DateTime,ACMKWHYTD_1 From WideHistory        Where  Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@DeadBand,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '

            if(@dec = 1)
                    begin
                            set @SqlQry = @SqlQry+ N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' )'
                    end

            if((@dec > 1) and (@dec < @NumberDays))
                    begin
                            set @SqlQry = @SqlQry+ N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) union '
                    end

            if(@dec = @NumberDays)
                    begin
                            set @SqlQry = N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) union '
                    end

            set @dec = @dec - 1

            SET @Start = CONVERT(Varchar(30),dateadd(dd,-@dec,GetDate()))
            SET @DeadBand= + CONVERT(Varchar(30),dateadd(ss,90,@Start))

    END
    print(@sqlQry)
    exec(@sqlQry)

I've seen quite a few tricks for converting a DATETIME into "just a time". Infact where I work there was quite some time spent finding the fastest. What people seem to forget is that "shortest code" is not always fastest, and often includes Implicit type conversions hidden away. The fastest we came up with is...

DATEADD(DAY, DATEDIFF(MINUTE, 0, ), 0)

The reason I mention it is that once you have the time component, everything becomes easier and allows for easier code changes for subtly different needs.

DECLARE
    @start_date    DATETIME,
    @end_date      DATETIME,
    @chosen_time   DATETIME
SELECT
    @start_date    = '2009 Jan 05 00:00',
    @end_date      = '2009 Jan 12 00:00',
    @chosen_time   = '1900 Jan 01 17:30'   -- '1900 Jan 01' is day 0

SELECT
    <whatever>
FROM
    WideHistory
WHERE
        [WideHistory].DateTime >= @start_date
    AND [WideHistory].DateTime <  @end_date
    AND DATEADD(DAY, DATEDIFF(MINUTE, 0, [WideHistory].DateTime), 0) = @chosen_time

One could easily adapt that to several key times of day, or a range of times, etc, etc...

EDIT:

As mentioned in comments to this answer, a change to the database structure would also benefit here. Having a DATE field as well as a TIME field. Often superfluous, but in this case could be beneficial.