Problem with Date Format conversion HTML_DB APEX

[SatanClaus]

Hello and good morning all together!

We are running an oracle 10g DB with Application Express (APEX, formerly HTMLdb).
I'm trying to create a little PL/SQL block that writes a sequence of dates into a table.

In this PL/SQL block i have an user variable 18_TERMVON
the input for this variable looks like this:

26/09/2006 10:41

In the beginning i declare a new variable TERMSTART:
(i have to convert it to_date, because there is a loop where i add TERMSTART + 1 [day] every round until ... doesn't matter)

DECLARE
  TERMSTART date := TO_DATE(:P18_TERMVON, 'DD/MM/YYYY HH24:MI');

[...]

Select
(to_date(substr(TERMSTART,1,10)|| ' ' ||substr(:P18_TERMVON,12,5), 'DD/MM/YYYY HH24:MI'),

[...]

The result i get is:

26/09/0006 10:41

but i need

26/09/2006 10:41

Can you tell me what goes wrong here?

When i Change the code to

Select
(to_date(substr(:P18_TERMVON,1,10)|| ' ' ||substr(:P18_TERMVON,12,5), 'DD/MM/YYYY HH24:MI'),

[...]

i get

26/09/2006 10:41

But this doesn't help me because i need the addition of one day after each cycle.

Hope i could express myself, so you can understand me.

Thanks in advance for your help.

Greetings from Germany,

SatanClaus

 

[Dagon]

I think that applying a function such as SUBSTR to a DATE is not advisable. SUBSTR is a character string function and was not intended to be used with dates. You would be better off declaring TERMSTART as a VARCHAR2(30) and then assigning it to 18_TERMVON.

 

[hoinz]

Hello,

I agree with Dagon that a substring of a date doesn't make that much sense.
On the other hand, I see that TERMSTART should have datatype date.
So I think you probably want trunc function instead.
Or if you insist on substr, then something like substr(to_char(TERMSTART,...

hope this helps

 

[Dagon]

I suspect the SUBSTRs aren't really needed. If the purpose is to go through some sort of loop and add 1 day whilst retaining the original hh24:mi, this could be done with something like:

declare
   begin_date date := TO_DATE(:P18_TERMVON, 'DD/MM/YYYY HH24:MI');
   new_date   date;
begin
   for i in 1..5 loop
      new_date := to_date(to_char(trunc(begin_date)+i, 'DD-MON-YYYY')||' '||
                          to_char(begin_date, 'HH24:MI'), 
                  'DD-MON-YYYY HH24:MI');
	  dbms_output.put_line(to_char(new_date, 'DD-MON-YYYY HH24:MI:SS'));
   end loop;
end;

 

[SatanClaus]

Hello dagon, hello hoinz,

thank you for your fast reply!
I think you convinced me of the trunc function. But i don't get the right effect.

when i use

TRUNC(TO_DATE(:P18_TERMVON, 'DD/MM/YYYY HH24:MI'))

i get
25.09.2006

but when i combine this truncated date with a substr of a varchar P18_TERMBIS) like

TRUNC(TO_DATE(:P18_TERMVON, 'DD/MM/YYYY HH24:MI'))||' '||SUBSTR(:P18_TERMBIS, 12, 5)

this is the result:
25.09.06 21:45
(see the year has only two digits...)
Now i want to convert it to_date again:

to_date(TRUNC(TO_DATE(:P18_TERMVON, 'DD/MM/YYYY HH24:MI'))||' '||SUBSTR(:P18_TERMBIS, 12, 5),'DD/MM/YYYY HH24:MI')

and there it is again:

25.09.0006 21:45:00

six years A.C.! thats way too early! ;-)

I'm open for your suggestions...

Bye,

SatanClaus

 

[Dagon]

The problem is that you have converted p18_termvon to a DATE and then truncated it. It is still a DATE at this point and will be converted to the NLS_DATE_FORMAT of your system, which is presumably DD-MM-YY. Change it to:

TO_CHAR(TRUNC(TO_DATEP18_TERMVON, 'DD/MM/YYYY HH24:MI')), 'DD/MM/YYYY')||' '||SUBSTRP18_TERMBIS, 12, 5)

 

[SatanClaus]

@Dagon:
Ohh, you were was faster then me!
;-(
I will take a look at your suggestion and then reply later.

so much to do besides programming in this (nearly-)one-man-it-department, that an answer lasts half an hour to write/submit

;-)
cu

 

[SatanClaus]

and again faster...
Don't you have to work? ;-)
Just joking!

 

[hoinz]

Dagon,

I am impresssed by your speed, too.
And btw, I fully agree.

 

[Dagon]

At the moment, I am somewhat underused by the company I am working for.

 

[SatanClaus]

@Dagon:
you don't need a co-worker?
I hope your at least overpayed when underused!

btw: Your last suggestion is what i was looking for. Gonna implement it now and re-write the other PL/SQL blocks i did wrong...

Until then: thank you Dagon and Hoinz! This Problem is solved.
Maybe i come back later with other probs... who knows...

cu
SatanClaus