Date Format

building reports webintelligence
1

Hi All,

Date format present at database level is DD/MM/YY
and I want to convert that prompts user response in date type.

=ToDate(Substr(UserResponse(“Select End date for Trade Start Ship Date”);1;10);“DD/MM/YY”)

Its giving error when I give date format as “DD/MM/YY”

Kindly guide me for the date format.

Thanks,
Amruta

megha_musale (BOB member since 2009-09-02)

2

And what format are you passing the value with in webI or Infoview, dont use substr and add the full format(time part if any) in the TODATE() function
.

haider :es: (BOB member since 2005-07-18)

3

As Hyder told remove substring and just have exactly the format of the date which you select/enter.

The reason why this gives out error is either one of the following.

  1. Incorrect date format.
    2.Substr(–;1;10) when you just have 8 characters totally in the “DD/MM/YY”

Jansi :india: (BOB member since 2008-05-12)

4

I have removed substr and directy given values but still its not working. :frowning: :frowning:

megha_musale (BOB member since 2009-09-02)

5

Please post the formula you use and also the date you selected in the prompt.

Jansi :india: (BOB member since 2008-05-12)

6

Formula used:

=ToDate(UserResponse(“Select End date for Trade Start Ship Date”);“DD/MM/YY”)

value selected : 12/9/2010 12:00:00 AM

megha_musale (BOB member since 2009-09-02)

7 
=FormatDate(ToDate(UserResponse("Select End date for Trade Start Ship Date");"dd/MM/yyyy hh:mm:ss a");"dd/MM/yy")

Jansi :india: (BOB member since 2008-05-12)

8

Jansi,

This code will work when we run the report in the view mode. If we run the report in edit mode the code will now work.

This is the date database value : 2009/12/04-19:31:57:896 and it is a character type.

I am using the prompt in my sql. this is the custom sql in webi XIR2.

to_date(SUBSTR(e.creationdate,1,10),‘yyyy/mm/dd’) between @prompt(‘Enter Begin Date’, ‘D’, Mono, Free) and @prompt(‘Enter End Date’, ‘D’, Mono, Free)

For example, You have suggested the below code,

=FormatDate(ToDate(UserResponse(“Enter End Date”);“dd/MM/yyyy hh:mm:ss a”);“dd/MM/yy”)

When we run the report in the view mode BO add the time and this should work.

But when we run the report in edit mode BO only add the date and we are getting the #Error.

Is this tool bug or any other formula to run the report in both modes

stalint (BOB member since 2004-12-14)