Round to nearest hour in a query (Access 2003)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 14 of 14

Thread: Round to nearest hour in a query (Access 2003)

  1. #1
    Join Date
    Feb 2008
    Location
    Tompkins County, NY, USA
    Posts
    20

    Round to nearest hour in a query (Access 2003)

    Hey-

    I am trying to create a query to sort items by date and blocks of time for the
    purpose of monitoring the usage of a facility that we own. In this query we will count the number of users who enter the database (It uses a logon Mechanism to record data) for when they entered the facility. We are going to group it by each hour.

    I've Googled this for a while with no avail. I do, however, have the date sorting down. Can someone please help? Or, if I am going about this wrong, just let me know. Any advice is appreciated.

    -Aquanut

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Show us how you are going about it.

    With respect to your question, what code do you have so far?

    Are you doing this in Access VBA?
    Last edited by Hack; 04-03-2012 at 07:56 AM.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2008
    Location
    Tompkins County, NY, USA
    Posts
    20
    I am just using an access query, I don't have anything besides the query sorting dates.

    I don't have the code, but haven't really found a way to use VBA in queries.

    but if it was possible to run a query in VBA, I don't know how to do it.
    I tend to use MySQL with a C++ front end I wrote, my work wants me to
    do this.

    -Aquanut

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You can use the Hour() function.
    SELECT StartDate, Hour(StartDate) As StartHour FROM MyTable ORDER BY StartDate DESC, Hour([StartDate]);

  5. #5
    Join Date
    Feb 2008
    Location
    Tompkins County, NY, USA
    Posts
    20
    kk, Thanks! you guys are a big help as always!!

  6. #6
    Join Date
    Feb 2008
    Location
    Tompkins County, NY, USA
    Posts
    20
    Actually, the hour function doesn't help. It justs returns the current hour. I need to be able to round it, due to the fact it is used to monitor usage. So ten minutes before each hour is on the next hour.

    Like I said before, I think I might be going about this wrong, but I don't us access very much.

  7. #7
    Join Date
    Feb 2008
    Posts
    162
    Assuming you want to round up for anything greater than 30 minutes past the hour. Try:

    SELECT StartDate, IIF(MINUTE(StartDate) > 30, HOUR(DATEADD("h", 1, StartDate)), HOUR(StartDate)) as StartHour
    FROM MyTable
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  8. #8
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    To Round Just add 30 minutes to the StartDate before using Hour()
    SELECT StartDate, Hour(DateAdd("n", 30, StartDate)) as AdjustedHour FROM MyTable

  9. #9
    Join Date
    Jul 2008
    Posts
    1

    Return a time value

    As I guess you wish a DateTime value to be returned, and a time within 30 minutes before Midnight to be rounded correctly, it would be easier to use TimeSerial:

    SELECT
    LoginTime,
    Int([LoginTime]) +
    TimeSerial(
    Hour([LoginTime]) + Minute([LoginTime]) \ 30,
    0,
    0) AS
    RoundedLoginTime
    FROM
    tblYourTable;

    Notice the use of \ which rounds down to the integer as Int does.

    /gustav

  10. #10
    Join Date
    Apr 2012
    Posts
    1
    Quote Originally Posted by Gustav View Post
    As I guess you wish a DateTime value to be returned, and a time within 30 minutes before Midnight to be rounded correctly, it would be easier to use TimeSerial:

    SELECT
    LoginTime,
    Int([LoginTime]) +
    TimeSerial(
    Hour([LoginTime]) + Minute([LoginTime]) \ 30,
    0,
    0) AS
    RoundedLoginTime
    FROM
    tblYourTable;

    Notice the use of \ which rounds down to the integer as Int does.

    /gustav

    You sir, are awesome..

    Here is what I did to get to the minute.
    Code:
    SELECT [Date],
    Int([Date]) + TimeSerial(Hour([Date]), Minute([Date]), 
    Second([Date]) \60 +60) As RoundedDateTime
    This rounds up to the nearest minute, to round down, simply remove the + 60
    Last edited by Hack; 04-03-2012 at 07:59 AM.

  11. #11
    Join Date
    May 2012
    Posts
    2
    Can anyone help me out please? I am using this expression to round time down (backwards) to the nearest 15 minutes - eg: 6:53 becomes 6:45. Works great.

    Expr1: TimeSerial(Hour([ENDTIME]),(Minute([ENDTIME])\15)*15,0)

    What I can't figure out is how to go the other way to round the time up (forwards) to the nearest 15 minutes - eg: 6:53 becomes 7:00.

    Expr2: TimeSerial(Hour([STARTTIME]),(Minute([STARTTIME]) ??? ,0)

    Is there a simple answer for this? Thank-you!

  12. #12
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Try This:
    ?TimeSerial(Hour([ENDTIME]),((Minute([ENDTIME])+14)\15)*15,0)

  13. #13
    Join Date
    May 2012
    Posts
    2
    Works perfectly. Thank you sir.

  14. #14
    Join Date
    May 2012
    Posts
    1
    Thanks for that, I was looking to do the same thing.
    Last edited by daniel5; 07-10-2012 at 06:24 AM.

Similar Threads

  1. Replies: 4
    Last Post: 05-29-2008, 05:39 PM
  2. Replies: 5
    Last Post: 05-08-2008, 09:09 AM
  3. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 05:01 PM
  4. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 12:22 PM
  5. Count Query, how to access?
    By Marcos in forum ASP.NET
    Replies: 2
    Last Post: 01-23-2001, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center