Kent's answer is the correct one on this post. Unless you alter the table definition to a column type other than datetime, or smalldatetime, the data will contain the time portion as stored in the database. If you don't want that, then you can store the data as a varchar(10), but then you lose the ability to compare values as dates should be compared. They instead are going to be compared as varchar strings, which isn't ideal, or appropriate.
Consider the following script:
Code Snippet
create
table test
(
rowid
int identity primary key,
datetimefield
datetime)
insert
into test select dateadd(dd, -1, getdate())
insert
into test select dateadd(dd, -2, getdate())
insert
into test select dateadd(dd, -3, getdate())
insert
into test select dateadd(dd, -4, getdate())
insert
into test select dateadd(dd, -5, getdate())
insert
into test select dateadd(dd, -6, getdate())
insert
into test select dateadd(dd, -7, getdate())
insert
into test select dateadd(dd, -8, getdate())
select
* from test
alter
table test
add
varcharfield varchar(10),
kalmansfield
varchar(10)
update
test
set
varcharfield = convert(varchar(10), datetimefield, 101),
kalmansfield
= convert(varchar(10), datetimefield, 111)
declare
@startdatetime datetime, @enddatetime datetime,
@startvarchar
varchar(10), @endvarchar varchar(10)
select
@startdatetime = convert(varchar(10), dateadd(dd, -5, getdate()), 101),
@enddatetime
= convert(varchar(10), dateadd(dd, -2, getdate()), 101),
@startvarchar
= convert(varchar(10), dateadd(dd, -5, getdate()), 111),
@endvarchar
= convert(varchar(10), dateadd(dd, -2, getdate()), 111)
select
*
from
test
where
datetimefield >= @startdatetime
and datetimefield < @enddatetime
select
*
from
test
where
kalmansfield >= @startvarchar
and kalmansfield < @endvarchar
select
@startvarchar = convert(varchar(10), dateadd(dd, -5, getdate()), 101),
@endvarchar
= convert(varchar(10), dateadd(dd, -2, getdate()), 101)
select
*
from
test
where
kalmansfield >= @startvarchar
and kalmansfield < @endvarchar
select
*
from
test
where
varcharfield >= @startvarchar
and varcharfield < @endvarchar
drop
table test
So long as you are always using the same convert switch, it works, but make one mistake and a simple one at that, and you results are wrong, and you probably won't catch it, at least not immediately, as has been my experience in more than one occasion reviewing report code that was using varchar(10) in a temp table to store date fields. The reports were always wrong, in some cases it took over 2 years for someone to actually notice.
Leave the data in datetime, and control display as that, display. That is my recommendation.
