Prev: PIVOT with dynamic where clause (how to)
Next: OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Unspecified error".
From: Luigi on 16 Mar 2010 06:00
Hi all,
having a table in SQL Server 2000 with a DateTime field and some values like:

ReferenceDate

2009-05-26 22:30:00.000
2009-05-27 12:30:00.000
2009-05-27 22:10:00.000
2009-05-30 22:40:00.000
2009-05-31 14:55:00.000

how can I write a Select that gets records that have DateReference =
getdate()?

So records where this field fall inside actual date.

Thanks in advance.

Luigi

From: Uri Dimant on 16 Mar 2010 06:21
Hi
SELECT * FROM tbl WHERE ReferenceDate>=GETDATE() AND
ReferenceDate<DATEADD(d,1,GETDATE())

Note if you have huge table to check for ranges ,created clustered index
on ReferenceDate




"Luigi" <Luigi(a)discussions.microsoft.com> wrote in message
news:7EA7E91C-7898-478F-A973-1C1D3A6A8034(a)microsoft.com...
> Hi all,
> having a table in SQL Server 2000 with a DateTime field and some values
> like:
>
> ReferenceDate
>
> 2009-05-26 22:30:00.000
> 2009-05-27 12:30:00.000
> 2009-05-27 22:10:00.000
> 2009-05-30 22:40:00.000
> 2009-05-31 14:55:00.000
>
> how can I write a Select that gets records that have DateReference =
> getdate()?
>
> So records where this field fall inside actual date.
>
> Thanks in advance.
>
> Luigi
>


From: Luigi on 16 Mar 2010 07:28
"Uri Dimant" wrote:

> Hi
> SELECT * FROM tbl WHERE ReferenceDate>=GETDATE() AND
> ReferenceDate<DATEADD(d,1,GETDATE())
>
> Note if you have huge table to check for ranges ,created clustered index
> on ReferenceDate

Perfect, thank you Uri.

Luigi
From: Plamen Ratchev on 16 Mar 2010 10:25
Since you have time portion different than midnight, you have to use half-open interval like this:

SELECT ReferenceDate
FROM Table
WHERE ReferenceDate >= DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010101')
AND ReferenceDate < DATEADD(DAY, DATEDIFF(DAY, '20010101', CURRENT_TIMESTAMP), '20010102');

--
Plamen Ratchev
http://www.SQLStudio.com
From: Luigi on 16 Mar 2010 12:51
A little problem.
Making this script:

Select DATEADD(day, DATEDIFF(day, 0, '18/01/2010 23:00:00'), 0)

I obtain this error:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

How can I solve?

Luigi
 |  Next  |  Last
Pages: 1 2
Prev: PIVOT with dynamic where clause (how to)
Next: OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Unspecified error".