grouping on date without time

Hello,

I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".

In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....

How can i group only on the date.

Vincent

Hello Vincent,

Right click on your group row for the dates, and select 'Edit Group...' In the 'Group on:' section, change your expression from =Fields!DateField.Value to =Format(Fields!DateField.Value, "MM/dd/yyyy")

Jarret

|||

Use this expression for grouping:

DateValue(Fields!DateField.Value)

This will set the time part of the datetime field to 00:00:00 and use only the date part. This gives better performance than formatting or any other solution.

Shyam

|||

Vincent,

I forgot to mention this in my last post...

You will probably want to show the date without the time as well, so just put the format statement as your textbox's expression in the group row. You could use Shyam's suggestion for the DateValue function (I didn't know it is more efficient, but I haven't noticed any performance degredation from using format), I just use the format for simplicity; both the group on expression and the textbox expression being shown will be the same.

Jarret

|||

Jarrett,

As you know, any operation (be it grouping or sorting or whatever) based on string is going to be more costlier (if not much more) than other datatypes and unfortunately Format function returns a string though we can still convert it using CDate and use that expression for grouping (which again becomes a 2 level conversion). Maybe there wont be a significant difference in performance unless there are millions of records.

Shyam

|||this is helpful
|||hi Lifesavers

I am getting this default format
3/6/2007 12:00:00 AM|||somehow I am using this following format and it seems to work.

=FormatDateTime(Fields!LOGINDTTIME.Value, 3)

now i am not able to sort it properly ...when i do sort it give following output

03/01/07 01:51:09 pm 03/01/07 11:46:35 am 03/01/07 04:42:53 pm 03/02/07 12:40:08 pm 03/01/07 03:56:04 pm

Please notice 03/01/07 11:46:35 am on second line, it is sorting it on numeric value not in am /pm...any ideas?
|||

Hello Anand,

If you want to display as 03/06/07 12:00:00 AM, you could use this: =Format(Fields!LOGINDTTIME.Value, "MM/dd/yy hh:mms tt"). The FormatDateTime uses your computer's regional settings to display the date/time.

As for the sorting on the table, you need to go to the Properties of the table, then click on the Sorting tab. Instead of using the Format in here, you should sort by the value in the field. You should be sorting by Fields!LOGINDTTIME.Value.

Jarret

|||

The best way to do it is to format the text box where you keep your date. (If it is a table it still will be a text box whithin a table)

So :

-> Right click -> Properties -> Format (tab) -> Format code: -> Ellipsis button [...] ->

... and here you have all kind of Standard formating e.g. date,time, currency ... Good Luck

|||

Hello Bernardo,

One of Anand's questions was how to format it like this 03/06/07 12:00:00 AM. Since there is no standard format that matches this, a custom format had to be used.

I'm not so sure that either way is a 'best' way (putting in a format code through the properties or using the format function), aren't they both doing the same thing? At least with the Format function, you can see the format directly in the textbox without navigating through the dialog box to find it or looking in the properties window.

Jarret

|||thanks Jarret,

now formating and sorting on date works as I wanted

thanks,
anand