0

I need the query which will fetch the data from DATE_TIME1 to DATE_TIME2 with interval of 2min or 3 min or 15 min which is variable.

The DATE_TIME data is available in the table in the interval of 1 min.
ex in the table:
29-Aug-2009 10:00:00
29-Aug-2009 10:01:00
29-Aug-2009 10:02:00
29-Aug-2009 10:03:00
29-Aug-2009 10:04:00
29-Aug-2009 10:05:00

I want query should be like this, fetch the data from table1 where DATE_TIME is between 29-Aug-2009 10:00:00 and 29-Aug-2009 10:30:00 with interval of 2 min. The out put of query will like this.

29-Aug-2009 10:00:00
29-Aug-2009 10:02:00
29-Aug-2009 10:04:00

Please help me to make query to meet result of query.
Thanks in advance.

4
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by ergen
0

Hi Dearjitu,

The following function serves you need:

CREATE FUNCTION dbo.fn_RoundTimeToMinIncrement
(
	  @TIME DATETIME
	, @MIN SMALLINT
)
RETURNS DATETIME
AS
BEGIN
	/* Ensure minutes is a positive number */
	SET @MIN = ABS(@MIN)
 	IF @MIN > 1440
	SET @MIN = 1440
 
	/* We are going to separate the DATE and TIME parts, do some math and add them together	*/
	RETURN
	DATEADD(DAY, 0, DATEDIFF(DAY, 0, @TIME))+ CAST(ROUND(CAST(CAST(CONVERT(VARCHAR(12), @TIME, 14) AS DATETIME) AS FLOAT) * 1440.0 / @MIN, 0) / (1440.0 / @MIN) AS DATETIME)
END
GO

To test this, use the following command

select dbo.fn_RoundTimeToMinIncrement(getdate(), 2)

The number in the second parameter is the interval who you need. In that case, 2 minutes.

0

You may want to use date tables and join your existing data table. I have a similar application where I do the exact same thing and I could not round the dates because I required a record every 15 minutes and couldn't have missing records due to missing data.

If your application doesn't require that then pv.vasconcelos's will suit your needs perfectly!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.