WebI convert String including timezone information to Date as input to FormatDate

[aj_shaik]

Hi,

I am trying to convert String with timezone to date and time format in WebI. So far luck dint favor me can somebody please give me proposals or if it is possible at all?

- Input String: 2007-04-30 13:10:02.0474381 -07:00

- To Format Date as:  yyyy-MM-dd HH:mm:ss to either local timezone or UTC timezone

Thank you in advance.

Ajaz

[Former Member · Accepted solution]

try [date] = ToDate(Left([input string];19);"yyyy-MM-dd HH:mm:ss")

CHeers,

ROgerio

[aj_shaik]

Hi,

Thanks a lot, for your responses.

Just for additional information, I was just wondering instead of parsing a String date and adding the offset using Relative Date, is it possible in WebI to have the "format" string including the timezone offset ?

So the ground Idea is that the MS SQL datetimeoffset datatype is not supported in HANA and I was expecting in WebI to accept a "format" string similar to yyyy-mm-ddThh:mi:ss.mmmZ.

Ajaz

[arijit_das]

If you want to subtract 7 hours from the date as your input string says, use RelativeDate function as below:

=FormatDate(RelativeDate(ToDate(left(Trim([Input String]);19);"yyyy-MM-dd HH:mm:ss");-7/24);"yyyy-MM-dd HH:mm:ss")

If the number of hours to be added/subtracted varies, then you need to separate that part from the input string using substr function and form the factor to be added or subtracted in relativedate function.

[former_member189638]

The above formula is correct. But I think you need to format it as well inorder to get the desired result.

=FormatDate(ToDate(Substr(Trim([DATE]);1;19);"yyyy-MM-dd HH:mm:ss");"yyyy-MM-dd HH:mm:ss")