By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,480 Members | 1,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,480 IT Pros & Developers. It's quick & easy.

How to group by date but not time?

Expert 5K+
P: 8,434
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(DateField,"dd/mm/yyyy") and this worked, but obviously sorted in the wrong sequence.
  2. Currently my GROUP BY column is set to DateOnly(DateField), and I have written...
    Expand|Select|Wrap|Line Numbers
    1. Public Function DateOnly(ByVal pDateTime As Date) As Date
    2.   ' Simple function to strip off the time, leaving only the date.
    3.   DateOnly = DateSerial(Year(pDateTime), Month(pDateTime), Day(pDateTime))
    4. 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.
Apr 17 '07 #1
Share this Question
Share on Google+
13 Replies


pks00
Expert 100+
P: 280
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,"short date")
from mytable
group by format(f1,"short date")
order by 1
Apr 18 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
If you GROUP BY Format(DateField,"dd/mm/yyyy") and ORDER BY DateField does this solve your problem?
Mary
Apr 18 '07 #3

Expert 5K+
P: 8,434
If you GROUP BY Format(DateField,"dd/mm/yyyy") and ORDER BY DateField does this solve your problem?
I'll try it, and let you know.
Apr 18 '07 #4

Expert 5K+
P: 8,434
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,"short date")
from mytable
group by format(f1,"short 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.
Apr 18 '07 #5

Expert 5K+
P: 8,434
If you GROUP BY Format(DateField,"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... :(
Apr 18 '07 #6

ADezii
Expert 5K+
P: 8,673
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?
Apr 18 '07 #7

Expert 5K+
P: 8,434
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(DateField,"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...
Expand|Select|Wrap|Line Numbers
  1. SELECT Format([DateField],"dd/mm/yyyy") AS MyDate
  2. FROM TableName
  3. GROUP BY Format([DateField],"dd/mm/yyyy")
  4. 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.
Apr 19 '07 #8

ADezii
Expert 5K+
P: 8,673
Woo! Looking good.

So far, I created a new query, put in field MyDate:Format(DateField,"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...
Expand|Select|Wrap|Line Numbers
  1. SELECT Format([DateField],"dd/mm/yyyy") AS MyDate
  2. FROM TableName
  3. GROUP BY Format([DateField],"dd/mm/yyyy")
  4. 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?
Apr 19 '07 #9

Expert 5K+
P: 8,434
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.
Apr 19 '07 #10

Expert 5K+
P: 8,434
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.
Apr 19 '07 #11

NeoPa
Expert Mod 15k+
P: 31,662
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.
Apr 20 '07 #12

NeoPa
Expert Mod 15k+
P: 31,662
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.
Apr 20 '07 #13

Expert 5K+
P: 8,434
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.
Apr 21 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.