sql - Comparing a Date column with a datetime value -

i have found below query in 1 our stored procedures

select *from table1 (convert(date,dateto) between @wkstdate , @wkenddate)) 

since usage of functions in where clause may hinder performance have changed below,

 select *from table1  dateto between @wkstdate , @wkenddate 

the result same after changing codes. not sure whether both give same result in time. scenarios above codes bring different results?

(p.s: @wkstdate , @wkenddate date values & dateto datetime value)

appreciate suggestions

this not yield same result.

let's dateto, datetime value, has time component:

'2015-09-21 01:00:00' 

your @wkenddate '2015-09-21'. where dateto between @wkstdate , @wkenddate not retrieve above row since '2015-09-21 01:00:00' > @wkenddate.

for more example:

create table tbl(dateto datetime) insert tbl      select cast('2015-09-21 00:00:00.000' datetime) union     select cast('2015-09-21 16:10:49.047' datetime) union     select cast('2015-09-22 16:10:49.047' datetime) union     select cast('2015-09-20 16:10:49.047' datetime)   declare  @wkstdate date = '20150921',          @wkenddate date = '20150921' select *  tbl dateto between @wkstdate , @wkenddate  select * tbl (convert(date,dateto) between @wkstdate , @wkenddate)   drop table tbl 

now, using function in where clause make query un-sargable there exceptions. 1 of them casting date.

another alternative if not want cast date not use between operator. instead use >= , <:

where     dateto >= @wkstdate      , dateto < dateadd(day, 1, @wkenddate)