[Oracle sql] varchar2 to date/time

[Verwijderd]

Ik heb een oracle database staan en iemand heeft daar ooit een tabel in aangemaakt zoals hieronder:

PK (number),
FK_afdeling (varchar2),
starttijd_1 (varchar2),
starttijd_2 (varchar2),
starttijd_3 (varchar2),
starttijd_4 (varchar2),
starttijd_5 (varchar2),
stoptijd_1 (varchar2),
stoptijd_2 (varchar2),
stoptijd_3 (varchar2),
stoptijd_4 (varchar2),
stoptijd_51 (varchar2),

Hierin staan de openings en sluittijden van bepaalde afdelingen binnen het bedrijf. Nu moet er een overzicht gecreerd worden waarin te zien is hoeveel uur elke afdeling bereikbaar is.

Ik zal dus een berekening moeten iets in de trand van:
(
(stoptijd_1 - starttijd_1) +
(stoptijd_2 - starttijd_2) +
(stoptijd_3 - starttijd_3) +
(stoptijd_4 - starttijd_4) +
(stoptijd_5 - starttijd_5)
)

Het probleem is echter dat ik het niet voor elkaar kan krijgen om deze varchar2 waarden om te zetten naar time waarden zodat de berekening goed verloopt. Ik weet dat ik het ergens moet zoeken in een TO_DATE oplossing maar ik kom er niet uit.....

Wie heeft er een hint, tip en of voorbeeld?

Thanks,
Johan.

[whoami]

code:

SELECT TODATE('14/12/2002', 'DD/MM/YYYY') from dual
SELECT TODATE('12/14/2002', 'MM/DD/YYYY') from dual

Zowiezo zou ik de tabel aanpassen -mocht dit mogelijk zijn- en die varchar velden naar datetime velden omzetten.

[Verwijderd]

Mjaaa,... dit bedoelde ik dus niet. Ik heb dus in die tabel waarden staan zoals 12.40 , 9.30 , 16.20 enz enz enz enz

Die wil ik dus voordat ik er mee ga rekenen eerst naar een officiele tijd omzetten het zal dus iets moeten zijn in de trand van

code:

select 
      to_date((select stoptijd_1 from tabel),????????) from dual

We werken dus met uren en niet met dagen... Ik heb dus nog niet door hoe ik dat met tijden moet doen........... Daar ben ik naar op zoek ;-)

whoami schreef op 12 januari 2004 @ 14:19:

code:

SELECT TODATE('14/12/2002', 'DD/MM/YYYY') from dual
SELECT TODATE('12/14/2002', 'MM/DD/YYYY') from dual

Zowiezo zou ik de tabel aanpassen -mocht dit mogelijk zijn- en die varchar velden naar datetime velden omzetten.

[whoami]

Ik zou zeggen: bekijk die TO_DATE functie gewoon nog eens goed in de help:

code:

select TO_DATE('9:30', 'HH:MI') from dual

[Verwijderd]

Ik snap de functie wel redelijk nu maar ik heb b.v. de waarde 9.00 staan 9 uur in de morgen dus... dat is de waarde van colum start_1_dag_1 waarvan de pk gelijk is aan 1000000001

code:

select 
      start_1_dag_1
from 
    vak_profiel 
where
     pk = '1000000001')

resultaat is 9.00

Maar dat wil ik dus als een tijd genoteerd hebben dus doen we het volgende:

code:

select 
      to_date(start_1_dag_1,'HH:MI')
from 
    vak_profiel 
where
     pk = '1000000001')

resultaat 01-JAN-04

Dus een andere aanpak is nodig dus heb ik het als volgt geprobeerd:

code:

select to_date((
              select 
                     start_1_dag_1
              from 
                 vak_profiel 
             where
                  pk = '1000000001'),'HH:MI')
 from dual

resultaat 01-JAN-04

Dus ik doe ergens iets fout want ik wil gewoon 9.00 zien maar dat de DB begrijpt dat we nu over een tijdsnotering praten. En dat krijg ik dus NIET voor elkaar.

whoami schreef op 12 januari 2004 @ 14:40:
Ik zou zeggen: bekijk die TO_DATE functie gewoon nog eens goed in de help:

code:

select TO_DATE('9:30', 'HH:MI') from dual

[whoami]

Je moet zowel een datum als een tijd terugkrijgen.

[Verwijderd]

zelfs als ik

code:

select TO_DATE('9:30', 'HH:MI') from dual

invoer dan krijg ik alleen maar 01-JAN-04 terug als waarde........ Ik snap er niks meer van

whoami schreef op 12 januari 2004 @ 15:50:
Je moet zowel een datum als een tijd terugkrijgen.

[DigiK-oz]

Als je bij to_date geen datum meegeeft, lijkt het erop dat Oracle daar dan gewoon 1-1-2004 invult (of 1 jan van het huidige jaar).

Als je die vervolgens met select opvraagt, is het default display-format van een DATE type dag-maand-jaar....Dus inderdaad 01-JAN-2004.

Het default display-format kan je dacht ik wel wijzigen (vraag me niet hoe, heb hier geen docs)

code:

 select to_char(to_date('9.30','HH24:MI'),'DD-MON-YYYY/HH24:MI') from dual

Als je een format meegeeft zie je dus dat de 09:30 wel in de tabel staat...

[Verwijderd]

Werkt perfect

Ik moet dus de waarde uit de tabel hebben en niet de waarde die ik zelf hard in de query inklop dus ik heb hem veranderd naar het volgende:

code:

select 
      to_char(to_date(stop_1_dag_1,'HH24:MI'),'HH24:MI')
from 
    vak_profiel 
where
     pk = '1000000001'

en ik krijg netje een correcte waarde terug 09.00 ;-) so far so good maar dat heeft er wel voor gezorgt dat we dus eigenlijk een rondje rond het probleem hebben gedraait want wat ik dus eigenlijk wil is het resultaat hebben van "stop_1_dag_1 - start_1_dag_1" zodat ik dus weet hoeveel tijd er tussen deze twee tijden zit. Als ik dat als een gewoon getal doe dan werkt het niet dus het moet als een tijd berekend worden..... omdat ik dus nu een to_char doe zit ik weer in een character en dat wil ik dus juist niet ik wil het als tijd hebben en aan het einde van de berekening dus pas als char...

enig idee hoe ik dat dan moet doen..... want op deze manier heb ik dus eigenlijk een rondje rond het probleem gedraaid.

Het zu dus uiteindelijk iets moeten worden zoals hieronder maar dat werkt dus niet.....

code:

select 
      to_char(to_date(stop_1_dag_1,'HH24:MI'),'HH24:MI' -
                   to_date(start_1_dag_1,'HH24:MI'),'HH24:MI'
                  )
from 
    vak_profiel 
where
     pk = '1000000001'

[DigiK-oz]

code:

select to_date(eindtijd,'HH24.MI') - to_date(starttijd,'HH24.MI') from tijd;

Geeft het verschil tussen de tijden in hele dagen, bijvoorbeeld 09.00 en 17.00 geeft 0,3333333 terug, dus 1/3 dag, dus 8 uur.

[justmental]

code:

select to_char(to_date(to_date(char2,'hh24:mi') 
                     - to_date(char1,'hh24:mi') * 86400, 'sssss'), 'hh24:mi')
from tabel

ongeveer of precies

[ Voor 22% gewijzigd door justmental op 12-01-2004 17:46 ]

[DigiK-oz]

justmental schreef op 12 januari 2004 @ 17:45:

code:

select to_char(to_date(to_date(char2,'hh24:mi') 
                     - to_date(char1,'hh24:mi') * 86400, 'sssss'), 'hh24:mi')
from tabel

ongeveer of precies

Met 2 extra haakjes precies

code:

select to_char(to_date((to_date(char2,'hh24:mi') 
                     - to_date(char1,'hh24:mi')) * 86400, 'sssss'), 'hh24:mi')
from tabel

[Verwijderd]

Het werkt perfect. Ik heb een aantal aanpassingen gedaan om de openingstijden van de afdelingen te bereken en eerst even met een dag uitgeprobeerd en dat werkt perfect het verteld me exact hoeveel uur de afdeling te bereiken is.

Nu kom ik echter op een ander vreemd iets uit als ik dit doe voor de hele werkweek dus in princiepe 5 keer een morgen en 5 keer een middag dan krijg ik een ORA-01830 error.

"ORA-01830: date format picture ends before converting entire input string"

Ik heb dus even de extra info opgezocht bij de error melding en de documentatie weet me het volgende te vertellen:

"ORA-01830: date format picture ends before converting entire input string
Cause: A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required. "

Dus ik heb regel voor regel gekeken of er een vreemd iets in zit en als ik een aantal regels verwijder dan komt opeens de ORA-01853 error naar boven.....

"ORA-01853: seconds in day must be between 0 and 86399
Cause: An invalid value for seconds in the day was specified in a date. Seconds must be between 0 and 86399 if the seconds past midnight format code (SSSSS) is used. "

Heel vreemd allemaal.... enig idee... Dit is de code die hij eigenlijk moet uitvoeren maar dat dus niet doet....

code:

select 
      to_char(to_date(
                      (
                        ( to_date(stop_1_dag_1,'hh24:mi') - to_date(start_1_dag_1,'hh24:mi') ) +
                        ( to_date(stop_2_dag_1,'hh24:mi') - to_date(start_2_dag_1,'hh24:mi') ) +                        
                        ( to_date(stop_1_dag_2,'hh24:mi') - to_date(start_1_dag_2,'hh24:mi') ) +
                        ( to_date(stop_2_dag_2,'hh24:mi') - to_date(start_2_dag_2,'hh24:mi') ) +                        
                        ( to_date(stop_1_dag_3,'hh24:mi') - to_date(start_1_dag_3,'hh24:mi') ) +                        
                        ( to_date(stop_2_dag_3,'hh24:mi') - to_date(start_2_dag_3,'hh24:mi') ) +                       
                        ( to_date(stop_1_dag_4,'hh24:mi') - to_date(start_1_dag_4,'hh24:mi') ) +                        
                        ( to_date(stop_2_dag_4,'hh24:mi') - to_date(start_2_dag_4,'hh24:mi') ) +                        
                        ( to_date(stop_1_dag_5,'hh24:mi') - to_date(start_1_dag_5,'hh24:mi') ) +
                        ( to_date(stop_2_dag_5,'hh24:mi') - to_date(start_2_dag_5,'hh24:mi') )                         
                      ) * 86399, 'sssss'), 'hh24:mi')
from 
    vak_profiel
where
     pk = '1000000001'

[justmental]

Verwijderd schreef op 13 januari 2004 @ 12:01:
"ORA-01830: date format picture ends before converting entire input string"

Er zit wat achter de komma in je seconden, wellicht door de 86399.

"ORA-01853: seconds in day must be between 0 and 86399
Cause: An invalid value for seconds in the day was specified in a date. Seconds must be between 0 and 86399 if the seconds past midnight format code (SSSSS) is used. "

Je zit tijden op te tellen, het resultaat is ws. > 1 dag.