remove time stamp from date

[former_member214617]

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

[Former Member]

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

[Henry_Banks]

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

[Former Member]

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

[former_member214617]

Hi

thanks for the reply...

I have tried with this code... but still getting the #ERROR

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

help me to correct the code...

regards

[Former Member]

Try like this:

Create following variables:

Selected Date = UserResponse("Start Date:")

Do Parse. What is the type of Variable ?? String or Date. ??

Can you see entered date correctly.

If Date:

Date Formated = ToDate([Selected Date];"dd/mm/yyyy") 

Do Parse. What is the type of Variable ?? Date??

If Date:

Final Date = FormatDate([Date Formatted];"mm/dd/yyyy") 

Do Parse.

Hope it solves the problem.

Note: We have to do it in steps, instead of doing on single var/fun.

Gracias...!!

[former_member214617]

hi

thanks for the reply

but this is also not solving the problem

if i have understood correctly then i need to create 3 variables in webi report as selected date, Date formatted, and final date when i have created selected date using the userresponse function then its type is string and when second variable is created using

ToDate([selected Date];"dd/mm/yyyy")

then it is giving an #ERROR

regards

[Former Member]

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

[Former Member]

Try this:

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

Gracias...!!

[former_member214617]

hi

thanks for the reply... but still getting same error.... have tried below both codes as well but after pressing enter getting #ERROR

ToDate(UserResponse("Strt Dt:");"mm/dd/yyyy")

and

FormatDate(ToDate(UserResponse("Strt Dt:");"mm/dd/yyyy");"DD/MM/YYYY")

please help to solve this.

regards

[Former Member]

It would be better if you can sample of your Date values.

and exact Prompt text used??

Gracias...!!

[former_member214617]

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