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 DateOnly(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.