DateAdd function 1

[shannonlp]

I need to create a formula field that adds time in both hours and minutes to an existing database field. I have a start date/time and need to add the duration to create an end date/time.

My start date field is metric_hist.datetime_dttm.
I have a @Duration field ({metric_hist.numeric_qty}/60)/60. It’s stored in seconds and I needed to convert it hours and minutes.
For my end date formula field I attempted to use this:
DateAdd ("h", {@Duration}, {metric_hist.datetime_dttm})

It works correctly when the duration is greater than 1 hour. However, I’m having a problem when the duration is < than an hour. I have many instances where the duration is 0.12, 0.25, 0.55, etc. When this is the case it does not add to the time. It works as expected when the duration is > 1 hour.

If someone could help I’d greatly appreciate it.

 

[pmax9999]

I think the problem you are encountering is that you are attempting to add fractions of hours, which it doesn't seem to like.

I tackled your challenge by using variables to convert the seconds to hours/minutes/seconds and then using DateAdd to add each of the time components to your Date/Time field:

WhilePrintingRecords;
Local NumberVar x := (Table.Column-TimeInSeconds);
Local NumberVar H := Int(x/60/60);
Local NumberVar M := Int(x/60) - (H*60);
Local NumberVar S := x - (M*60) - (H*60*60);

DateAdd('s', S, DateAdd('n', M,DateAdd('h', H, {@metric_hist.datetime_dttm})))

Replace "(Table.Column-TimeInSeconds)" with the field/formula that represents the total seconds.

Hope this helps.

Cheers
Pete

 

[shannonlp]

Thanks for your response.

I created a formula field using this following syntax:

WhilePrintingRecords;
Local NumberVar x := {metric_hist.datetime_dttm};
Local NumberVar H := Int(x/60/60);
Local NumberVar M := Int(x/60) - (H*60);
Local NumberVar S := x - (M*60) - (H*60*60);

DateAdd('s', S, DateAdd('n', M,DateAdd('h', H, {metric_hist.datetime_dttm})))

I'm getting the following error:

'A number is required here' and it highlights {metric_hist.datetime_dttm} (line 2).

Is it because NumberVar requires a number and {metric_hist.datetime_dttm} is a datetime field?

 

[shannonlp]

Hi,

Please disregard my earlier post. This got me what I needed.

DateAdd ("s",{metric_hist.numeric_qty},{metric_hist.datetime_dttm} )