Change hours to another value

[Former Member]

Hi,

I have underlying bex datetime field (STRING)  [FinishTimstmp] which is in "dd.MM.yyyy hh:mm:ss" format...

I want to convert this field to a populate a new value where ever it finds date value from 16.09.2014 24:00:00 >> 16.09.2014 23:59:59.

I do get 2 values in that Bex field (STRING)- eg. 16.09.2014 00:00:00 or 16.09.2014 24:00:00.

When converting this STRING (date) field in webi by using TODATE() function webi interprets 16.09.2014 00:00:00 to 16.09.2014 12:00:00 and where it finds 16.09.2014 24:00:00 it throws an #ERROR.

I want to calculate the difference between this date field and with currentdatetime to give me the difference in HOURS.

Any clues ?

A.B.

[Former Member]

Hi Abhishek,

Please see Peter's reply in the discussion below:

I have tried it and it works well.

Regards,

Ashvin

[Former Member]

Thanks for the link...

I have been using the same formula to calculate the difference in hours but my issue is webi throwing up an error right at the low level where it fails to convert string to a date type when using TODATE function and especially where the original hour value is in 24:00:00.

So i need a fix for webi to convert that date in a proper format for that hour_difference formula to work.

hope it makes sense ?

[Former Member]

Abhishek,

It seems logical as 24:00:00 is not a valid time format. It's either 23:59:59 or 00:00:00.

I think that converting the data type to varchar(50) may work.

Or may be you can check out the display formats available in WEBI by going to the date column in the report. Click on the Properties box and find the TEXT FORMAT section and click on the "Number Format" property to select the display format.

This will allow you to process dates as dates and display the dates as you please then you can calculate the difference.

I hope this helps.

Regards,

Ashvin