<div>
datetime data type store both date and time. If you insert only the date in it, it will add the time part to it '00:00:00'.<br />
You can't do anything about it.<br />
If your only concern is about displaying, you can very well convert it as you already know it. What's wrong with a varchar type? It's just for display, right? You are not doing any date/time calculations based on that.<br />

</div>


<div>
Yes, that is correct. In SQL2008 you can have just the date part.<br />
<br />
The underlying table / data element is date time. You can present that in a variety of date formats &nbsp;using the convert function with a style code. The convert function can convert to or from a string. If the resultant data type is datetime, then you will always have both components.<br />
<br />
There is of course one string that is also inmplictly understood as a date if comparing / using in conjunction with dates and that is yyyymmdd or style code 112<br />
<br />
There is another but becomes language dependant, and that is dd MMM yyyy or style code 106 - in english databases you can use '03 FEB 2009' as an implictly understood datetime but use that same string in say spanish, or french, and it will crash and burn. So, only good in same language environments.<br />
<br />
What can sometimes need to happen is express columns according to their subsequent use, if presentation, then have to have the desired result, if however for a report writer or similar other &quot;raw&quot; feed then can keep as datetime :<br />
<br />
e.g. <br />
create table #dt (date datetime)<br />
insert #dt values ('20090101')<br />
insert #dt values ('20090202 22:22:22')<br />
insert #dt values ('20090303 03:03')<br />
insert #dt values ('20090404')<br />
<br />
select date as raw_date_time<br />
&nbsp; &nbsp; &nbsp; &nbsp;,convert(varchar,date,106)<wbr /> as text_date<br />
&nbsp; &nbsp; &nbsp; &nbsp;,convert(varchar,date,112)<wbr /> as yyyymmdd<br />
&nbsp; &nbsp; &nbsp; &nbsp;,convert(varchar(10), date, 103) as ddmmyyyy<br />
&nbsp; &nbsp; &nbsp; &nbsp;,convert(varchar(10), date, 101) as mmddyyyy<br />
&nbsp; &nbsp; &nbsp; &nbsp;,convert(datetime,convert(<wbr />varchar,da<wbr />te,106)) as datetime_text_date<br />
&nbsp; &nbsp; &nbsp; &nbsp;,convert(datetime,convert(<wbr />varchar,da<wbr />te,112)) as datetime_yyyymmdd<br />
from #dt<br />
<br />
<br />

</div>


<div>
<div class="content wysiwyg-content">
I have a sql variable 'StartDate' of type DATETIME that shows for example 2/6/2009 12:00:00. &nbsp;But I only want it to show 2/6/2009 and still keep it as DATETIME type. &nbsp;I know I can use the convert function, but it switches the variable to a character type.
</div>
</div>


I have a sql variable 'StartDate' of type DATETIME that shows for example 2/6/2009 12:00:00.  But I only want it to show 2/6/2009 and still keep it as DATETIME type.  I know I can use the convert function, but it switches the variable to a character type.

sql keep only date part in datetime format

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.