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]

Re: Records newer than 90 minutes (smalldatetime)

On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:
[color=blue]
>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[/color]

Hi Chris,

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

Best, Hugo
--

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

[Ross Presser]

Re: Records newer than 90 minutes (smalldatetime)

On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:
[color=blue]
> 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[/color]

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]

Re: Records newer than 90 minutes (smalldatetime)

On Mon, 8 Nov 2004 16:45:45 -0500, Ross Presser wrote:
[color=blue]
>On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:
>[color=green]
>> 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[/color]
>
>SELECT * FROM Tbl WHERE DateDiff(m,time _column,GetDate ()) BETWEEN 0 AND 90
>
>And you'd better have an index on your time_column.[/color]

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]

Re: Records newer than 90 minutes (smalldatetime)

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in
news:kpuvo0l9ue gi5iuute1h7rte1 3cp34dsh6@4ax.c om:
[color=blue]
> On Mon, 8 Nov 2004 16:45:45 -0500, Ross Presser wrote:
>[color=green]
>>On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:
>>[color=darkred]
>>> 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[/color]
>>
>>SELECT * FROM Tbl WHERE DateDiff(m,time _column,GetDate ()) BETWEEN 0
>>AND 90
>>
>>And you'd better have an index on your time_column.[/color]
>
> 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[/color]

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