Date part of DateTime

I'm trying this simple query in SQL Server 2005:
SELECT * FROM tblRecords
WHERE InputDate = '1/30/2007'

I have two records which have value for InputDate as
1/30/2007 12:34:12
1/30/2007 11:23:32

But the query returns no records due to the time part of the date.
How do I pick only the date part for comparison and discard the time part in the InputDate ?

try this...

SELECT * FROM tblRecords
WHERE datediff(day,inputdate,'1/30/2007') = 0

|||I dont think you need to compare month and year...its redundant|||

You're right, apologies. It's been a long day. I've scrubbed my previous post.

Chris

:)

|||

A few approaches:

This trick relies on the

fact that datetime values are represented internally as floating point

values. The integer part represents the date, and the fractional part

the time. So rounding down to the nearest integer value gives you a

date.

DECLARE @.d datetime
SET @.d =

GETDATE()
SELECT @.d
SELECT CAST(FLOOR(CAST(@.d AS

float)) AS datetime)

However, doing the CAST/FLOOR

trick on a column name will probably prevent the optimizer from using

indexes on that column. This is more useful when you want to get just

the date part of GETDATE() and check it against date-only values in a

column.

In this case, you can

probably do something like this:
SELECT * FROM

tblRecords
WHERE InputDate >= '1/30/2007' AND InputDate

< '2/1/2007'

Or if you're comparing against a

variable, this would probably do the trick:
SELECT * FROM

tblRecords
WHERE InputDate >= @.idate AND InputDate

< DATEADD(dy, 1, @.idate)

|||

I prefer David's reply:

SELECT * FROM tblRecords
WHERE InputDate >= '1/30/2007' AND InputDate < '2/1/2007'

This method can take advantage of potential indexes that might be available on the inputDate column. Note that in the following mockup that David's method gets an INDEX SEEK plan whereas the plans of the queries that enclose a function around the inputDate field will use either a CLUSTERED INDEX or a TABLE SCAN:

create table dbo.tblRecords
( rid integer not null
constraint pk_tblRecords primary key,
inputDate datetime not null,
filler char (300) not null
)
go

create index inputDate on dbo.tblRecords(inputDate)
go

update statistics dbo.tblRecords
go

insert into dbo.tblRecords
select iter,
cast(cast(cast('3/15/5' as datetime) as float)
+ 730 * dbo.rand() as datetime),
'Record # ' + convert(varchar(5), iter)
from small_iterator (nolock)

go

--set showplan_text on
go

select *
from tblRecords
where inputDate >= '1/30/2007'
and inputDate < '1/31/2007'

go

--set showplan_text off
go

-- - 47 Rows Returned -

- rid inputDate filler
-- -- -
-- 4400 2007-01-30 00:08:11.790 Record # 4400
-- 10836 2007-01-30 00:27:30.883 Record # 10836
-- ...
-- 21253 2007-01-30 10:14:39.810 Record # 21253
-- 7795 2007-01-30 11:07:24.507 Record # 7795

-- StmtText
-- --
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[tblRecords]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[tblRecords].[inputDate]), SEEK:([tblRecords].[inputDate] >= Convert([@.1]) AND [tblRecords].[inputDate] < Convert([@.2])) ORDERED FORWARD)

-- Table 'tblRecords'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 0.

SELECT * FROM tblRecords
WHERE datediff(day,inputdate,'1/30/2007') = 0

StmtText
-- -
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[tblRecords].[pk_tblRecords]), WHERE:(datediff(day, [tblRecords].[inputDate], 'Jan 30 2007 12:00AM')=0))

-- Table 'tblRecords'. Scan count 1, logical reads 1313, physical reads 0, read-ahead reads 0.

|||Thank you all for the help provided.
The table is already created and filled and I cant change anything in the table.
I'll use one of the above methods to get the result|||

Some

time it is required to use Convert even though it decrease the performance

SELECT * FROM tblRecords

WHERE convert(Varchar(10),InputDate,101) = '30/01/2007'