Need help selecting only unique days from datetime

ryodoan

Well-known member
Joined
Jul 16, 2004
Messages
65
Location
Ohio
Programming Experience
3-5
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

Well-known member
Joined
Jul 12, 2005
Messages
95
Location
Arkansas
Programming Experience
5-10
Add this as the last line in your Query:

VB.NET:
ORDER BY  CONVERT(char, dtMsgDate, 1)

Optionaly you can end the line with "DESC" to put the most recent date at the top.
 
Last edited:
Top Bottom