Need help selecting only unique days from datetime

[ryodoan]

I am fairly new to SQL server and the commands it can use. I am working on a website chat logger interface for a group I am in right now. Basically it will allow people to search / view the chat logs from a web interface.

At the moment I have a table in my database that holds the messages, as in:

tblMsgs
- pkMsgID
- fkUserID
- vcMsgText
- dtMsgDate

Each message has the time it was sent, so for ex. "11/10/2007 5:43:04" I want to get a table that just has the distinct DATES minus the time.

So I want to get a return table that looks like:
11/10/2007
11/11/2007
11/13/2007
ect..

At the moment I wrote a stored procedure as follows:

SELECT DISTINCT dtMsgDate
FROM tblMsgs
ORDER BY dtMsgDate

However, it basically ends up returning a datetime for every single message because if the time is different it sees it as a new, distinct date. I use the time when I am displaying the message as a way of ordering it.

I found a website that suggested:

SELECT DISTINCT CONVERT(char, dtMsgDate, 1) AS chrDate
FROM tblMsgs

And that worked in that it displays the unique dates, but it fails in that the dates are no longer ordered...

I want to use the returned results of this stored procedure to populate rows in a drop down box so people can select a date range of the chats to display.

 

[WellsCarrie]

Add this as the last line in your Query:

ORDER BY  CONVERT(char, dtMsgDate, 1)

Optionaly you can end the line with "DESC" to put the most recent date at the top.