Records newer than 90 minutes (smalldatetime)

[Chris]

Hi,

I have a table in SQLServer2000 where e.g. the actual time is insert for
every record (smalldatetime). But how do I select every records newer
than 90 minutes?

/Chris

[Hugo Kornelis]

On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:

Hi,

I have a table in SQLServer2000 where e.g. the actual time is insert for
every record (smalldatetime). But how do I select every records newer
than 90 minutes?

/Chris

Hi Chris,

SELECT Column1, Column2, ..., ColumnN
FROM MyTable
WHERE InsertionTime > DATEADD(minute, -90, CURRENT_TIMESTAMP)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Ross Presser]

On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:

Hi,

I have a table in SQLServer2000 where e.g. the actual time is insert for
every record (smalldatetime). But how do I select every records newer
than 90 minutes?

/Chris

SELECT * FROM Tbl WHERE DateDiff(m,time_column,GetDate()) BETWEEN 0 AND 90

And you'd better have an index on your time_column.

[Hugo Kornelis]

On Mon, 8 Nov 2004 16:45:45 -0500, Ross Presser wrote:

On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:

Hi,

I have a table in SQLServer2000 where e.g. the actual time is insert for
every record (smalldatetime). But how do I select every records newer
than 90 minutes?

/Chris

SELECT * FROM Tbl WHERE DateDiff(m,time_column,GetDate()) BETWEEN 0 AND 90

And you'd better have an index on your time_column.

Hi Ross,

As far as I know, that index won't be used in your query, since the
time_column is embedded in a function call. You'd have to have the
time_column all by itself at one side of a comparison operator (like in my
query) for the index to be useful.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Ross Presser]

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in
news:kp********************************@4ax.com:

On Mon, 8 Nov 2004 16:45:45 -0500, Ross Presser wrote:

On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:

Hi,

I have a table in SQLServer2000 where e.g. the actual time is insert
for every record (smalldatetime). But how do I select every records
newer than 90 minutes?

/Chris

SELECT * FROM Tbl WHERE DateDiff(m,time_column,GetDate()) BETWEEN 0
AND 90

And you'd better have an index on your time_column.

Hi Ross,

As far as I know, that index won't be used in your query, since the
time_column is embedded in a function call. You'd have to have the
time_column all by itself at one side of a comparison operator (like
in my query) for the index to be useful.

Best, Hugo

Thank you for clarifying my fuzzy thinking, Hugo. Your query is much better
in that respect.