Date Part of the date time

I have data in a smalldatetime column but the dates are
mixed. Some in the '3/7/04 11:47:00 AM' form and some
in '3/7/04' (This is when I open the table from EM).
When I select from the Query Analyzer I get
'2004-01-08 13:35:00'
My question is How can I select only the date part of it
(Not the time part) to compare with another column ?
Thnaks for any help.......select convert (varchar, <date>,101) returns the date in mm/dd/yyyy format.
It comes out as a string. That can easily be compared against another date.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...

quote:

---

> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

---

|||SELECT CONVERT(SMALLDATETIME, CONVERT(CHAR(8), column, 112)) FROM table
You can wrap this in a function for slightly cleaner code and slightly more
overhead. Depending on the size of your table, of course. I have a
function called dbo.getDayFloor() that handles this for me.
Note: I always convert to the smaller of the two datetime datatypes if I'm
only dealing with day boundaries and not concerned about time.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:099201c3db71$adcdd100$a601280a@.phx.gbl...

quote:

---

> I have data in a smalldatetime column but the dates are
> mixed. Some in the '3/7/04 11:47:00 AM' form and some
> in '3/7/04' (This is when I open the table from EM).
> When I select from the Query Analyzer I get
> '2004-01-08 13:35:00'
> My question is How can I select only the date part of it
> (Not the time part) to compare with another column ?
> Thnaks for any help.......

---

sql