Master calender with hours

[ashis]

Hi ,

I want to create master calendar with hour. My script looks like this

MasterCalender: 
LOAD 
Date,
  Year(Date) AS Year, 
'Q' & Ceil(Month(Date) / 3) AS Quarter,

Month(Date) As Month,
Date(MonthStart(Date),'MMM') as Month1,
Date(MonthStart(Date),'MMM-YYYY') as MonthYear,
Year(Date) * 12 + num(Month(Date)) as [Month Counter],
Day(Date) As Day, 
WeekDay(Date) AS WeekDays,
Hour(Date) as Hour,
Week(Date) As Week; 
Load 
Date(MinDate + IterNo() -1 ) AS Date

While (MinDate + IterNo() - 1) <= Num(MaxDate); 
Load 
Min(Start_Time) AS MinDate, 
Max(End_Time) AS MaxDate  

RESIDENT acb; 

Start_Time and End_Time have date format like 'DD-MM-YYYY hh:mm:ss:ttt'  .

The hour field in above only returning 22 .

Could you please help.

[adityaakshaya]

Hi Ashis,

Hour() function only return integer value.

You can use Timestamp(Date,'hh:mm:ss:ttt') and it will give you exactly what you are looking for.

Regards,

Akshaya

PS - If you find response as correct/helpful then mark it Correct/Helpful

[ashis]

Hi Akshaya,

Thank you for the reply.

Timestamp(Date,'hh:mm:ss:ttt') will format the date field as hour:min:sec am/pm . Actually I am not looking it .

I am looking to generate hours in (24 hours) such as 00, 01, 02 ....23.. in my master calendar.

[snehamahaveer]

Hello Ashis Sau,

You can do the following to create hour values.

1. Hour(Timestamp#(Date)) - this would return an integer value. Eg 08 hours would be '8'

2. Time(Date,'hh') - this would return the hour with 2 digits Eg 08 hours would be displayed as '08', same can be achieved by Timestamp(Date,'hh')

Hope this helps.

Regards,

S