Skip to Content

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

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 05, 2014 at 09:48 PM

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

    CHeers,

    ROgerio

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 05, 2014 at 10:08 PM

    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")

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 06, 2014 at 06:02 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 08, 2014 at 07:29 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.