Timestamp to 1/2 Hour Interval?

[mistahpat]

Again, I am new to Access.

I have a table of call data with timestamps such as: 12:07:14 PM, and wish to translate that in my query to a corresponding 1/2 hour interval. So for each record in the query results, I should be able to see both: 12:07:14 PM and 12:00 PM.

Is there an easy way to this?

 

[Galaxiom]

Do you want it rounded to the nearest half hour or rounded down?

 

[Galaxiom]

There are many different ways to do this and it also depends on the datatype required for the result.

Functions that can be useful include CDate(), DatePart(), TimeSerial(), Format(), Round(), Int().

You should be able to combine these techniques to get what you want. All untested code following.

This one returns a string with the rounded down value:

DatePart("h",[timestamp]) & ":" & Iif(DatePart("n",([timestamp]<30),"00","30")

If that result needs to be in a DateTime datatype then use:

TimeSerial(DatePart("h",[timestamp]), Iif(DatePart("n",([timestamp]<30),0,30),0)

It is a bit more complex to do normal rounding by splitting the parts like this because the the hours are affected by the minutes rounding. An alternative is to round the time itself.

 CDate(Round([timestamp]*48)/48)

Round down with this expression:

 CDate(Int([timestamp]*48)/48)

The problem with this technique is rounding error because half hours don't always come out exactly as decimal parts of a day. Consequently you may find parts of a second included. These can become a problem when you compare times.

This is why imes should always be compared using DateDiff(). However because the rounded times are derived very differently from times entered directly so it is possible that DateDiff() may still have problem.

One of the other techniques can then be applied to these results to get the results into line.

Also note that Round() uses Bankers Rounding where values exactly in the middle will round to the even whole number.

 

[mistahpat]

Thanks for your response/advice. I need it rounded down. I am going to try to implement something you've stated here, but as I'm new, I may end up with more questions. Lol.

 

[mistahpat]

I've used, CDate(Int([time]*48)/48), and that seems to be working! Not sure what might not, but I guess I'll cross that bridge if I come to it. =)