Skip to Content

remove time stamp from date

hi guys

Had start date and end date as prompt in webi report and showing same date value in report header using UserResponse function but it is showing time with date, and in report both columns are showing in dd/mm/yyyy format, have tried using FormatDate(UserResponse("Start Date:"); "dd mmm yyyy"), it is giving an error

" The expression/sub-expression uses an invalid data type"

please tell me what is wrong in the formula

thanks & regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 28, 2010 at 04:10 PM

    Try this:

    =FormatDate(UserResponse("StartDate");"DD/MMM/YYYY")

    Gracias...!!

    Add comment
    10|10000 characters needed characters exceeded

    • hi

      Start Date: is the prompt text and format can be any

      Date in the database is like :- 01/10/2010,28/09/2010

      ToDate(UserResponse("Start Date:");"mm/dd/yyyy")

      and

      FormatDate(ToDate(UserResponse("Start Date:");"mm/dd/yyyy");"DD/MM/YYYY")

      hope this will help you

      thanks & regards

  • avatar image
    Former Member
    Oct 29, 2010 at 08:48 AM

    Hi,

    The function UserResponse() returns a character string and not a field of type Date.

    You will need to treat it as a string and when you want to convert it to a date use the function

    =ToDate(date_string;format)

    Regards,

    Harry

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member leo dec

      Hi,

      It is creating an #ERROR when the format you assign to the ToDate function does not match the date in the string.

      E.g. if you have a user response like '1/1/1998 12:00:00AM' you could use the formula:

      =FormatDate(ToDate(Left(UserResponse("prompt");8);"M/d/yyyy");"dd MMM yyyy")

      to get a result as: 01 JAN 1998

      However this fails when the date changes to 10/1/1998 or 1/10/1998 or 10/10/1998. In those cases the format of the ToDate function should be: "MM/d/yyyy" or "M/dd/yyyy" or "MM/dd/yyyy".

      I know it's a lot of work, but the best solution is to create variables for month, day and year:

      1. variable for month v_month

      =Left([prompt_date];Pos([prompt_date];"/")-1)

      2. we need to get rid of the first part of the string to find the second '/' so we create a dummy variable dummy1

      =Right([prompt_date];Length([prompt_date])-(Length([v_month])+1))

      3. variable for day v_day

      =Left([dummy1];Pos([dummy1];"/")-1)

      4. create a second dummy variable dummy 2 to get rid of the day part of the string

      ==Right([dummy1];Length([dummy1])-(Length([v_day])+1))

      5. variable for year v_year

      =Left([dummy2];4)

      Just assuming that the year is always 4 positions in the prompt string.

      Now you can create your variable for displaying the date in the format you want:

      ==FormatDate(ToDate(FormatNumber(ToNumber([v_month]);"00")+FormatNumber(ToNumber([v_day]);"00")+FormatNumber(ToNumber([v_year]);"0000");"MMddyyyy");"dd MMM yyyy")

      Note: I was just assuming that your date format is always month/day/year. If the format is different then you need to change the variables accordingly.

      Hope this helps

      Harry

  • avatar image
    Former Member
    Oct 04, 2011 at 04:32 PM

    Hi sap_@2011

    How did you resolve the 'timestamp' removal issue? I have the same issue. Can you please let me know.

    i have tried the Replace function too. the issue is what ever other functions we use, the results would be in string format. How can we keep it in/ convert to date format? that would solve i guess.

    Tnx

    Edited by: kayrao on Oct 4, 2011 6:32 PM

    Add comment
    10|10000 characters needed characters exceeded

    • This was an old thread resurrected... you may not get a reply

      I'd suggest you try the steps outlined above, or search the knowledge base in service marketplace for Notes

      Regards,

      H