WebI Date Conversion

Posted byDavid Taylor 5 Comments on WebI Date Conversion

Have you ever been in a situation where you wanted to display a date on a report but the date was in date/time format?

I run into this all the time when dealing with detailed data from transactions with date time stamps.

The best way I’ve found to do it is by converting the value from DateTime to Text and back to Date.

Convert Text to Date

Believe it or not, it’s not too bad after you figure it out.  It did take me a few tries so I thought I’d share my solution with you.

=ToDate(Left(UserResponse("As Of Date");
Pos(UserResponse("As Of Date");" ")-1);"M/d/yyyy")

Let’s break it down so we can understand the individual components.

In this case, the field I am formatting is a date/time prompt and I will reference it by the name “As Of Date”.

I will need to perform 3 steps in able to convert it:

  1. Use the Pos function and find the space between the date & time, e.g. 1/1/2011 15:03:03 returns the position value of 9.
  2. Next use the Left function and grab everything to the left of the space – 1.  e.g. 1/1/2011 15:03:03 returns the text “1/1/2011”.
  3. ToDate now takes the date text and converts it to a date.  e.g. 1/1/2011 becomes a date type field 2011-01-01

Display As Text

Optionally now that I have the value in a date-field format, I can now convert it back to a formatted date using the FormatDate function.

=FormatDate(ToDate(Left(UserResponse("As Of Date");
Pos(UserResponse("As Of Date");" ")-1);"M/d/yyyy");"yyyy-MM")

What about you?

What’s your favorite WebI syntax for converting data types in WebI?

Do you have any conversions that are giving you trouble?

«Good BI»

5 replies on “WebI Date Conversion”

  1. Hi David,

    What about if the users are not using the same regional settings. Example French versus English: it can be 01/01/2010 00:00:00 or 1/1/2010 00:00:00

    do you think that this gonna work two.

    Kindest regards,

  2. Also, one issue I have found with this is that depending on how you enter the date using the prompt, if you leave off the time, this formula will create an error, or blank actually because the POS return value is 0 and doing a Left function on -1 leaves you with nothing since there is no space found. To be fully functional in all cases, it is best to test for Pos function result greater than zero for using the Left function, and if not, then just leave out the Left function altogether.

  3. Here is a formula that works for converting a string with and without the time attributes (hh:mm:ss A) to a date data type.

    =If(Length(UserResponse(“StartDate1:”))>10;ToDate(Left(UserResponse(“StartDate1:”);Pos(UserResponse(“StartDate1:”);” “)-1);”MM/dd/yyyy”);ToDate(UserResponse(“StartDate1:”);”M/d/yyyy”))

    Best regards,
    Heidi H.

  4. Hi,

    I want to apply a formula in my report “MonthsBetween”. it is the difference between current date and estimated date. but the problem is that estimated date has values like “201505” how do i go about this??

Comments are closed.