Array of hour integers between two dates

[#1]

Hello everyone,

Does anyone know of a way to find if an hour exists between two dates? For example:

Start Date = 03/18/2015 03:00: PM

End Date = 03/19/2015 07:00 AM

So how could one test to find it the hour of 08 (8 am) falls between these two dates?

Sincere thanks for any help.

Sheldon

[jaimeaguilar]

You can create a field in script like this:

time(maketime(hour(DT)),'hh TT') as Hour

this way you'll have a field that contains: 12 am, 1 am , 2 am and so on (you'll have up to 24 possible values, according to every hour in a day), so you can filter an ocurrence in time, rounded to hours

hope this helps,

regards

[jaimeaguilar]

Just replace DT with your Date field,

regards

[#4]

Thanks Jamie, I am going to try that.

[Anonymous]

You can create an interval table with all possible combinations.

See attached.

[#6]

Thanks Jamie that was helpful, but what I am really looking for is a way to use the match function to test if the hour exists between two dates.

[jaimeaguilar]

Ok, then try with interval match function, just like Michael suggested,

regards

[#8]

Thanks Mike, this is almost what I am looking for. The qvw you attached is very clear. What I am trying to achieve is the time value of the hour intervals between two dates. For instance their are nine hourly intervals between 03/18/2015 08:00 PM and 03/19/2015 05:00 AM. The interval Function returns them as 1,2,3,4,5,6,7,8,9. What I am looking for is something that will return the hour intervals as the actual hour values, like 20, 21,22,23,00,01,02,03,04,05.

[Anonymous]

I'm sure there is a better way to do this but it seems to work.