BusinessObjects Board

Datetime prompt not working with MSSQL

building reports webintelligence legacy
#1

Hi there,
I’m using WebI 4.2.

The problem I’m having is that when the prompt is used to filter out dates while using a universe, which is using a MSSQL database, I am getting the following error message: “[Microsoft][ODBC SQL Server Driver][SQL Server] The conversion of varchar data type to a datetime data type resulted in an out-of-range value…”

When I tried using the same prompt functionality while using a universe that is using an Oracle database it worked ok with no issues.

You help in this would be greatly appreciated, since I am really stuck with this issue.

Many thanks again in advance

Pan :uk: (BOB member since 2013-05-10)

#2

To help you out, please post the data type of the column from the database (I assume it’s varchar) and the code used for the prompt. Also, what is the data type of the column in the universe?

charlie :us: (BOB member since 2002-08-20)

#3

Hiya Chaz,

First of all I would like to thank you for your time.

I’ve check the columns in question:
a) in the database they are defined as datetime data types,
b) on the universe they are defined as date data types
c) the code used for the prompt is “@prompt(‘Enter Start Date:’,‘D’,Mono,Free,Persistent,User:0)”

Thanks

Pan :uk: (BOB member since 2013-05-10)

#4

Try it with first of Jan, then with 31st December.

Could be a format issue.

Damocles :uk: (BOB member since 2006-10-05)

#5

Hiya,

It looks like it is a formatting issue.

Thank you very much for spotting it :smiley:

Would you by any chance know, how I can change the formatting that is being used by the prompt?

Many thanks

Pan

Pan :uk: (BOB member since 2013-05-10)

#6

It could simply be that the default format expected by your DB engine is not the one being used by your input locale.

Change one of them.

Damocles :uk: (BOB member since 2006-10-05)

#7

After changing the locale in the BI launch pad to English (USA) the problem was resolved.

Thanks again for all your help.

Pan :uk: (BOB member since 2013-05-10)

#8

Pleasure.

Happy to help.

Damocles :uk: (BOB member since 2006-10-05)