Date/Time format from Oracle 9i

building reports desktopintelligence
1

Hi,

Have a time stored in an Oracle Database (9i) as number(5,2).

Normally I’d use the to_date function to specify what I want eg
to_date(table.column, ‘HH24:MM’).

However, when I do this I get the following error :cry: :

Error during SQL execution: (DA0003)

Details:
Exception: DBD, ORA-01840: input value not long enough for date format
State: N/A

I suspect it’s something to do with the decimal place but have tried
a few things unsuccessfully.

Anyone any ideas?

Thanks.

Steve

SteveR :australia: (BOB member since 2004-08-27)

2

A long way:
You could convert your NUMBER to a STRING (using To_Char), then SubStr, finally To_Date

Andreas :de: (BOB member since 2002-06-20)

3

Thanks Andreas.

I was under the impression to_char was for converting a date to text, not number to text.

I’ve tried this though:

to_date(substr(to_char(MSV893_ALW.END_TIME),3,2),‘MM’)

and get this:

Error during SQL execution: (DA0003)
Exception: DBD, ORA-01858: a non-numeric character was found where a numeric was expected State: N/A

I think this is because of the decimal place because the column is a number(5,2) type in Oracle although I’m not sure.

Do you know how I can get rid of the decimal place?

Steve

SteveR :australia: (BOB member since 2004-08-27)

4

You could use the Translate function or Pos and SubStr (all Oracle I believe) to find the “,” and replace it etc., details would depend on the database.

You can always troubleshoot your problem step by step instead:
Create a dimension object defined as

substr(to_char(MSV893_ALW.END_TIME),3,2)

Look at the LOV and see if you spot anything such as a comma etc. that would cause a problem when you try to convert it to a date. Then take it from there.

Andreas :de: (BOB member since 2002-06-20)

5

How is the actual time stored? You say decimal(5,2) which means - I assume HH.MM, or hours to the left of the decimal, and minutes to the right. Is this correct?

If so you would retrieve the hours by

trunc(timefield)

and the minutes by

(timefield - trunc(timefield)) * 100

Use the to_char() function next (and yes, it is what is called an overloaded function, it is used to convert date values to character strings and also to convert number values to character strings).

Now you have hours and minutes. Concatenate them with || ‘:’ || and you have a time. Now all of that is based on the fact that the time 5:15 would be stored as 5.15 so if that is not correct, please let us know exactly what the format is. :slight_smile:

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

6

Aah, cooking with gas now. Thanks Dave and Andreas.

Managed to get the following results using the following syntax for the object “End time (HH:MM)” = to_char(trunc(MSV893_ALW.END_TIME)) ||’:’|| (to_char(MSV893_ALW.END_TIME - trunc(MSV893_ALW.END_TIME)))*100:

End Time End Time (HH:MM)
23.10 23:10
23.15 23:15
23.20 23:20
23.30 23:30
23.50 23:50
23.59 23:59
24.00 24:0

You asked what the data looked like Dave: The End Time column is what it looks like via Reporter. In LOV it looks like:

23.1
23.15
23.2
23.3
23.5
23.59
24

Two problems now I have to crack:

  1. Making the minute part = 00 where it’s 0. Hopefully a format number or similar should help here. Or maybe to_date function?

  2. There’s no such thing as 24:00 so do I need to use a decode or similar to transpose to 00:00.

Thanks again.

Steve

SteveR :australia: (BOB member since 2004-08-27)

7

Your error only comes op because TO_DATE only converts your numeric fields to a date. Not character fields. So you’ll have to convert it to a number, without decimals.

jobjoris :netherlands: (BOB member since 2004-06-30)

8

The zero can be added with the lpad() function. I think the syntax would be

lpad(minute,2,'0')

Replace “minute” with the code that extracts the minutes and converts to character.

Oracle lpad() function

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