How to select data between given interval of time from SQL

Question

I have following data in SQL

Need sql queue:

I have 10 tags, it is continuously logging to SQl at every 10 Seconds of intervals. Now i want create report like.....User will provide time interval and tag name on that basis it will select data from table & display in Excel Suppose My inputs are : interval is 2016-09-21 14.00.00 to 2016-09-21 22.00.00 Tag Name is : system\Year I want following output: Suppose record is not present at that time, then i need blank/Zero value. I want to create this in VB6.

Answer 1

You need to use an SQL statement that selects the fields you want from the records you want. You said you are trying to select only the even minutes so the SQL is something like

SELECT
    fields
FROM table
WHERE
    (DateAndTime BETWEEN '2016-09-21 14:00' AND '2016-09-21 22:00')
AND
    (DATEPART(MINUTE, DateAndTime) % 60 = 0)

If you need even seconds also just add another AND to the the WHERE clause.

SELECT
    fields
FROM table
WHERE
    (DateAndTime BETWEEN '2016-09-21 14:00' AND '2016-09-21 22:00')
AND
    (DATEPART(MINUTE, DateAndTime) % 60 = 0)
AND
    (DATEPART(SECOND, DateAndTime) % 60 = 0)

To answer the question in the last comment, I have worked up a SQL query that works. It may be that if you post your exact requirements with a SQL tag and ignore the vb6, vba tags because this is really all SQL and has nothing to do with the environment running it, that you'll get a better answer. This will work. I would suggest incorporating this into a stored procedure.

This works by creating a table variable and populating it with the possible time values that can be returned. It then left joins the table you want to query. The result is a null value from the second table when there is no record to join on.

--select records on the hour from 8:00 to 5:00
DECLARE @MinTime datetime = '2016-9-9 09:00'   --need to parameterize
DECLARE @MaxTime datetime = '2016-9-10 17:00'  --need to parameterize
DECLARE @TempTime datetime = @MinTime

DECLARE @Times TABLE
(
    TimeValue datetime
)
WHILE @TempTime <= @MaxTime
BEGIN
    INSERT INTO @Times
    (
        TimeValue
    )
    VALUES
    (
        @TempTime
    )
    SET @TempTime = DATEADD(hour, 1, @TempTime)
END

SELECT
    CONVERT(varchar(10), TimeRange.TimeValue, 101) AS [Date]
    ,CONVERT(varchar(10), TimeRange.TimeValue, 108) AS [Time]
    ,ISNULL(Tag, 'NA')
    ,ISNULL(Value, 0)
FROM
    @Times TimeRange
LEFT JOIN
    table
ON
    (CONVERT(datetime, CONVERT(varchar(10), table.DateAndTime, 101) + ' ' + CONVERT(varchar(2), DATEPART(HOUR, table.DateAndTime)) + ':' + CONVERT(varchar(2), DATEPART(MINUTE, table.DateAndTime)) + ':' + CONVERT(varchar(2), DATEPART(SECOND, table.DateAndTime))) = TimeRange.TimeValue)
WHERE
    (TimeRange.TimeValue BETWEEN @MinTime AND @MaxTime)