Format date in dd-mm-yyyy format in Oracle

building semantic layer
1

Hello!

I am stuck on creating an object in universe that is pointing to Oracle. This objects is prompt for the date. I have created the object with following SQL but when I run the report, it throws an error ‘Invalid number’.

To_Date(To_Char(TRUNC(@Prompt(‘Please enter Run Date’,‘D’,mono,free), ‘MM’), ‘mm-dd-yyyy’))

If I input the date on the report, it will look like:

To_Date(To_Char(Trunc(‘02-04-2011 00:00:00’, ‘MM’),‘mm-dd-yyyy’)) And ‘02-04-2011 00:00:00’

Your help will be greatly appreciated.

Thanks,
Matt

Mattdos11 (BOB member since 2006-02-07)

2

Why are you converting from date to character and then back again? Why not simply leave the prompt as a date?

Dave Rathbun :us: (BOB member since 2002-06-06)

3

I am no Oracle expert but this might be because the locale of the db is set to only show date format of dd-mm-yyyy.

I tried this SQL against my db:

select to_date(to_char(sysdate,'mm') ||'-'||to_char(sysdate,'dd')||'-'|| to_char(sysdate,'yyyy'),'mm-dd-yyyy') from sys.dual

I got back:

A better way would be to set the prompt to yyyymmdd numerical format.

Uvee :uk: (BOB member since 2005-10-26)

4

Thanks for the reply.

@Uvee:
I dont know what you mean by locale of db, but when I use sysdate, the format it returns is DD-MON-YY and hence sysdate will work when it is used in the TRUNC function. However, since I have a prompt and the report inputs the date in ‘DD-MM-YYYY HH:MM:SS’ format, the TRUNC function does not work.

Sorry: forgot to mention that I am trying to get the First day of the month. Hence, I am using Trunc()

Thanks,
-Matt

Mattdos11 (BOB member since 2006-02-07)

5

I got the solution. Thanks for your time, guys!!

FYI: I used the following:

Trunc(to_date(substr(@Prompt(‘Please enter Run Date’,‘D’,Mono,Free),1,10), ‘dd-mm-yyyy’), ‘MM’)

Mattdos11 (BOB member since 2006-02-07)