DISTINCT to ShortDateString, not DISTINCT to the DateTime; How?

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHEREBranchID=@.prmBranchID ANDProposal_Date=@.prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

Try this -

Select Distinct Convert(char(10), Proposal_Date, 101)

It will eliminate the time part from the selection

Regards

Ash

|||

This one will solve your problem:

SELECT

DISTINCTCONVERT(NCHAR(8), Proposal_Date, 112)AS Proposal_Date

FROM

Proposals

You can look up from Books Online for the date, convert functions to help you understand more about the date and time. If you need more help, please post again.

|||

Hello,

Yeah that was what I neeed to know, and it solved the problem.

(apparently, I'm still new to T-SQL :)

Thanks.

|||

I know your problem is resolved but try the link below for more about the SQL Server DateTime convert function codes. And if you want Short Datetime you use SmallDateTime data type it gives you DateTime less seconds because it has less resolution. Hope this helps.

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

DISTINCT only certain columns in on table and pull their IDs

Please help.? (I am using ASP, VB, SQL)

I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

So for example I might have?

ID 1 - Office 1 - Chicago

ID 2 - Building 2 - Chicago

So I want to show

Office 1 - Chicago (my link will contain the ID in it)

So far I have

SELECT DISTINCT City , State FROM dbo.Offices WHERE (City IS NOT NULL) AND name like 'office%' ORDER BY City ASC

This works as far as pulling unique cities and names but now I need to get those City's ID. Can I use DISTICT for certain columns? Or how do I do it so that when it does pull out the unique cities it pulls their IDs as well?

?? Select ID and (DISTINCT City , State) FROM dbo.Offices ??

Thank you.

sscoder,

are you saying that the cities id is in another table. And are you just looking for the set of distinct city, state, id 's? is so just add id into your select statement

select distinct ID, City, State FROM Offices

if this is not what you are asking, can you include a bit more info...-- jp

|||City, State, ID are in the same table.

I want to find unqiue cities and then their IDs.?I'm using the ID in the hyperlink for a string.?

If I add ID to the distinct query?it will take priority and it will not list out unique cities. So instead of displaying

Chicago
Florida
Las Vegas

It will display this if I add ID to distinct
Chicago
Chicago
Chicago
Florida
Florida
Las Vegas

I need unique cities and then their ids.? Is there a way?

Thank you.

|||i see what you are saying but it you want the distinct city and a city may have more than 1 id, then how do you know which id you want to be returned?|||would only one of the city records be of type 'office'?|||there are many cities but each office has a different name. The every main office name start with 'Office' so I only want to pull up unique cities with name starting with 'Office%'. So there will never be two same cities. Only one office per city. That's where I put "WHERE name like 'office%' "

So the list will be:

Chicago - Office 1

Florida - Office 2

Las Vegas - Office 3

The query does this already. I just can't get the ID for that one row. That's what I need help on. If that "Office 1" in "Chicago" has an ID of 2. I want to get that. How??