Date Format

building semantic layer
1

Hello There,

I am writing a derived query and in that me writing the below formula for where to date input values .It’s giving an error for me.

Action_Time between to_date(@prompt(‘Enter Action Time(Start):’,‘D’,mono,free),‘DD-MM-YY’) and to_date(@prompt(‘Enter Action Time(End):’,‘D’,mono,free),‘DD-MM-YY’)

Here the action_time values in DB are like this 02-MAY-12 10.36.11.581000000 PM

As we know while running the report it’s take the values from the calender mm-dd-yyyy.

I have tried no. of ways but no luck.

Could you please assist me here to correct format.

Thanks in advance

Shravan :hb:

shravan_bo :india: (BOB member since 2006-04-25)

2

Can you tell us the error message, as well as the database you are using?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Database is Oracle10G and Error is "ORA-01830 data format picture ends before converting entire input string ORA-02063 preceding lind from LVRPTD "LVRPD (This is our database name)

Thanks in advance

Shravan

shravan_bo :india: (BOB member since 2006-04-25)

4

Try this:

to_date(Action_Time,'DD-MM-YY') between to_date(@prompt('Enter Action Time(Start):','D',,mono,free),'DD-MM-YY') and to_date(@prompt('Enter Action Time(End):','D',,mono,free),'DD-MM-YY')

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

No luck,

Still getting the same error.

shravan_bo :india: (BOB member since 2006-04-25)

6

What is the data type of that field in the database?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

7

It’s Timestamp(6)

Thanks for reply

shravan_bo :india: (BOB member since 2006-04-25)

8

Have you tried formatting the time portion then returning the date? Example:


Action_Time between to_date(to_char(@prompt('Enter Action Time(Start):','D',,mono,free),'DD-MM-YY hh:mi:ss'),'DD-MM-YY') and to_date(to_char(@prompt('Enter Action Time(End):','D',,mono,free),'DD-MM-YY hh:mi:ss'),'DD-MM-YY')

Judy

JMulders :us: (BOB member since 2002-06-20)