Date/Time query

[dearjitu]

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.

[pv.vasconcelos]

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.

[sknake]

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!

[ergen]

Hi,

This one can be helpful: http://jacek-szarapa.com/index.php?p=sql&d=42