Round up time to the start of hour

[#1]

How to round up time to the start of hour? Say,

Time               Return

03:05 AM        03:00

07:45 PM        19:00

I want to know whether it's attainable by Set Analysis or by Data Load. Thanks!

[Kushal_Chawda]

=time(hour(time#(TimeField,'hh:mm TT')),'hh:mm') as TIME

[Kushal_Chawda]

or

hour(time#(TimeField,'hh:mm TT'))&':'&00 as TIME

[sunny_talwar]

May be this:

Time(Round(Time, 1/24), 'hh:mm') as Time

or

Time(Floor(Time, 1/24), 'hh:mm') as Time

Althouth I am not sure how you converted 7:45 PM to 17:00 -> did you mean 20:00 or 19:00 intead? First expression will get you 20:00 and second will get you 19:00

[swuehl]

Try

Set TimeFormat = 'hh:mm TT';

LOAD

     Time as TimeOriginal,

     Time(Floor(Time, 1/24), 'hh:mm') as Time, // adapt the time format as needed, if you want to show AM/PM

...

FROM ...;

edit: this will of course perform a rounding to the lower hour value 07:45 --> 07:00

[sunny_talwar]

7:00 or 19:00 Stefan?

[#7]

Sorry, should be 19:00 instead. Thanks!

[sunny_talwar]

Try this:

Time(Floor(Time, 1/24), 'hh:mm') as Time

[swuehl]

Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

7:00 or 19:00 Stefan?

19:00. Missed the PM on the second line

07:45 PM --> 19:00

May, if the above doesn't work, please check that your input records are correctly read in as time, having a numeric representation, e.g. by using Time#() interpretation function or setting the default format.

See also following, also applies to time values:

Why don’t my dates work?

[#10]

Thanks, Sunny! You are right that it returns the rounded up time value but why are there duplicates?

I am intending this to use as a dimension, I expect that the measures will be aggregated according to the unique time value.

Any idea why it behaved like that?