How to group by date but not time?

[Killer42]

Hi all.

Hopefully a simple one for any SQL guru. In an Access query, how can I group by a date field, without having my data broken down by time? In other words, I just want a count per day, not per second or whatever.

I'm using Access 2003, but I doubt it matters in this case.

At present I am just using a function (see below) to strip off the date, but I consider this terribly inefficient.

I did try some searching, but this is one case where there's too much info available. Just within TSDN there are dozens upon dozens of postings mentioning grouping by date, but they seem to mostly relate to reasons why or why not, different situations where one should group by dates, and so on.

What I've tried so far:

1. First I tried setting the GROUP BY column to Format(DateFiel d,"dd/mm/yyyy") and this worked, but obviously sorted in the wrong sequence.
2. Currently my GROUP BY column is set to DateOnly(DateFi eld), and I have written...
   

   Public Function [B]DateOnly[/B](ByVal pDateTime As Date) As Date
     ' Simple function to strip off the time, leaving only the date.
     DateOnly = DateSerial(Year(pDateTime), Month(pDateTime), Day(pDateTime))
   End Function

   This does leave me the benefits of using an actual date field, including the ability to apply a format without stuffing up the sort sequence.

[pks00]

If u have grouped the date field, u now have your data in short format

Next thing is ordering, have u tried specifying column numbers?

eg

select format(f1,"shor t date")
from mytable
group by format(f1,"shor t date")
order by 1

[MMcCarthy]

If you GROUP BY Format(DateFiel d,"dd/mm/yyyy") and ORDER BY DateField does this solve your problem?
Mary

[Killer42]

If you GROUP BY Format(DateFiel d,"dd/mm/yyyy") and ORDER BY DateField does this solve your problem?

I'll try it, and let you know.

[Killer42]

If u have grouped the date field, u now have your data in short format

Not sure what you mean by this. If you mean I've got just the date, then yes. But I'd prefer not to have to call my function for every record to produce the date to group on.

Next thing is ordering, have u tried specifying column numbers?
eg
select format(f1,"shor t date")
from mytable
group by format(f1,"shor t date")
order by 1

I'll try, but don't see how that will help. It's already ordered by that column, by default. The problem is that the formatted date is useless for ordering purposes. As an example, they are sorted...
01/01/2003
01/01/2004
01/01/2005
01/01/2006
01/01/2007
01/02/2003
01/02/2004
01/02/2005
01/02/2006
01/02/2007
This is pointless, even if it was U.S. format. I can get the right sequence if I format it as YYYYMMDD, but then only a geek will recognise the date.

[Killer42]

If you GROUP BY Format(DateFiel d,"dd/mm/yyyy") and ORDER BY DateField does this solve your problem?

Nope. It won't let me ORDER BY a field unless it's one of the ones specified in the aggregate functions.

Sigh... :(

[ADezii]

Nope. It won't let me ORDER BY a field unless it's one of the ones specified in the aggregate functions.

Sigh... :(

Try Ordering by DateField, set it to an Aggregate Function such as Min, Max, and deselect the Show Box. Does this produce your desired results?

[Killer42]

Try Ordering by DateField, set it to an Aggregate Function such as Min, Max, and deselect the Show Box. Does this produce your desired results?

Woo! Looking good.

So far, I created a new query, put in field MyDate:Format(D ateField,"dd/mm/yyyy"), then added DateField again with Total: Max and Sort: Ascending.

It appears to work. Here's the resulting SQL, which I feel we may still be able to refine a bit...

SELECT Format([DateField],"dd/mm/yyyy") AS MyDate
FROM TableName
GROUP BY Format([DateField],"dd/mm/yyyy")
ORDER BY Max(TableName.DateField);

(All table and field names have been changed to protect the innocent).

Of course, when I tried removing the Max( ) function in the SQL, I got the old "must be part of an aggregate function" message.

One thing I noticed was that this appeared to execute much faster than the version using my function. Which is why I didn't want to use the function in the first place, of course.

[ADezii]

Woo! Looking good.

So far, I created a new query, put in field MyDate:Format(D ateField,"dd/mm/yyyy"), then added DateField again with Total: Max and Sort: Ascending.

It appears to work. Here's the resulting SQL, which I feel we may still be able to refine a bit...

SELECT Format([DateField],"dd/mm/yyyy") AS MyDate
FROM TableName
GROUP BY Format([DateField],"dd/mm/yyyy")
ORDER BY Max(TableName.DateField);

(All table and field names have been changed to protect the innocent).

Of course, when I tried removing the Max( ) function in the SQL, I got the old "must be part of an aggregate function" message.

One thing I noticed was that this appeared to execute much faster than the version using my function. Which is why I didn't want to use the function in the first place, of course.

Is this liftoff?

[Killer42]

Is this liftoff?

Very possibly. I have the "real" query running now. It may still take quite some time, but we'll see.

It was a bit odd when I ran the function version yesterday. After a while the CPU that Access was using dropped a bit (um... to around 30-40%, I think) but Explorer jumped up to nearly 50% CPU, and stayed that way for maybe half an hour before I gave up (it was affecting other things too much) and cancelled the query. I wonder what Explorer was doing...

If was Access using too much CPU, I could just turn down the priority, but turn down Explorer and you don't really improve matters.

Oh well. I'll see how the current one goes. Shouldn't have to run it very often.

[Killer42]

Whoa! It finished, for the whole 4½ years' data, in a pleasingly short amount of time. Nice!

The version using the function looked as though it was going to run for quite a few hours, if it didn't manage to kill the PC first. And that was only for something like 6 months' worth.

However, I may be misleading you a bit here. There is another (proably much bigger) factor which should be taken into account. The old version was using a heap of queries UNION'd together. The new one works the same, except that I've gone to the raw tables rather than the queries. In testing, this seems to make a differenc. Hardly surprising, since the queries include lookups from at least one other table, plus calculated fields.

All in all, the switch from query to table probably made a bigger contribution, but hopefully getting away from the function has also helped. And I really hate having to have an external function in my SQL like that. It's just one more thing to worry about.

[NeoPa]

Sorry to come in to the party late Killer.
DateValue() is a built-in function (like format) which simply returns a Date type Variant value with no time part included.
If you want to use Format() then it's a good idea to use the elements in descending order (Y M D) rather than any localised setting. This ensures that the result that you GROUP BY is the same as that you ORDER BY and doesn't require an extra set of function processing within each record (of a fair number I think). Easiest is probably DateValue() though.

[NeoPa]

Something else for you specifically (I wouldn't normally recommend this as it relies on the behind-the-scenes nature of Date/Time storage), would be to use the Int() function which simply strips the fractional (and therefore Time) part.

[Killer42]

Something else for you specifically (I wouldn't normally recommend this as it relies on the behind-the-scenes nature of Date/Time storage), would be to use the Int() function which simply strips the fractional (and therefore Time) part.

Thanks for that. The Int() function sounds like a neat solution.

As for DateValue() function, I did look into that. Didn't like what I saw, but I forget why.

And as for using Format() function, I'm doing that to format them the way we use them. If I could just use YMD then I wouldn't have hd the problem in the first place. In effect, I needed to group (or ORDER) by YYYYMMDD but display as DD/MM/YYYY.

Problem is solved now, but thanks for the input anyway.