Crystal 8/ SQL 2000 - Date/Time Field Problem 1

[swbradbury]

Hello!

I am currently working on a report for a Help Desk in which I need to capture a "distinct count" of dates but I am having problems due to the Date/Time Field in the database. I am hoping someone can possibly tell me how I can fix it...

Here's an example of what is happening:

I have a few records that have been entered over the course of a few days and they are time stamped as follows:

Sept 2, 2002 11:30AM; Sept 2, 2002 3:27PM;
Sept 4, 2002 2:12PM;
Sept 6, 2002 9:54PM; Sept 6, 2002 4:10PM;

Now what I would like to have happen is that when I run the report it counts the dates as 3 distinct dates: Sept 2, Sept 4, and Sept 6... What I am getting now is 5 distinct dates: Sept 2, 2002 11:30AM; Sept 2, 2002 3:27PM; Sept 4, 2002 2:12PM; Etc...

Does anyone know of a way that I can get the report to count the days without having the report factor in the time? Given the software that is being used I can't split the database date/time field into two separate fields...

Any help would be much appreciated.

Thanks,
SWBradbury

 

[cmmrfrds]

Use the convert function in your select statement to strip off the time.

Select convert(varchar(10), yourdate,101) from table

 

[dgillz]

An easier way to do this is in crystal reports, under file, options, select the "convert date/time foeld to date" checkbox.

File-Options in crystal is a global setting. If you want this to be for this report only, select the same checkbox under file, report options. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[Guest_imported]

You need a Group by clause.
eg. Select columns from table
Group By Date time

If you combine this with the previous
Select convert(varchar(10), yourdate,101) from table
as suggested by 'cmmrfrds' that should do the trick.