Re: to_date format

mattjones_at_hotmail.co.uk wrote:
> Hi
> Im trying to convert part of a string into a date. So far i have:
> $dbh->do ("INSERT INTO TEST4 (log_name) VALUES (TO_DATE('$filename',
> 'YYYYMMDDHH24'))");
>
> where:
> $filename = 2006090603_103_fs.log
>
> i need the 2006090603 to be changed into a date and placed itno a field
> but i also need the '103' placed into a seperate field.
>
> The above statement is giveing me the error :
> RA-01830: date format picture ends before converting entire input
> string
>
> I think its because i am telling it what to do with the '2006090603'
> part of the name but then it doesn't know what to do with
> '_103_fs.log'. Is there a format so the sql omit's this last bit of
> information?
>
> Any thoughts on this?
>

Hi Matt,

if your filename consists always of the date followed by an underscore followed by something else, then you can just take the substring up to the first unterscore:
TO_DATE(SUBSTR('$filename',1,INSTR('$filename','_')-1),'YYYYMMDDHH24')

hth
Peter Received on Tue Sep 19 2006 - 06:17:11 CDT