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

Displaying Total number of Rows in a Report in Page Header.

Hi,

I have requirement to display Total number of Rows in a Report in Page Header.

I have written the following code in Page header it shows RowCount for the Page only.

=Count(ReportItems!textboxInTableCell.Value)

Can anyone please help on this?

Regards

Raghav

By Total number of reports in the report do you mean the number of rows returned by the Dataset query? If so, add a textbox in your Report Body with the expression =CountRows("DataSet1") with the name of your Dataset in place of DataSet1.

Then refer to this textbox directly in the Page Header.

This should give you the total row count for your Dataset.

-Aayush

|||

Thanks aayush,

I used =CountRows() in body header and set the RepeatWith property to "tableName" and it works as Page header.

Regards

Raghavendra