Re: to_date format

Brian Peasland wrote:
> MattJ83 wrote:
> >> Have you considered concatenating '00' to the end of your string and
> >> then using the format mask 'YYYYMMDDHH24MI'? I haven't tested it, but
> >> I'm not sure that Oracle truncates the minutes.
> >>
> >> HTH,
> >> Brian
> >
> > Unfortunatly i can't do that as the string is actually part of a
> > filename. There are alot of files so to go through all of the filenames
> > and change them to that format would be inefficient and time consuming
> > (there maybe 100+ files at the moment and they are generated every
> > hour!
>
> Who says you have to change the file names? Why not change the string?
> You should be using a TO_DATE function here. And you pass a string to
> that function. Why not modify that string in the function? Instead of:
>
> TO_DATE(string,'YYYYMMDDHH24')
>
> you would have:
>
> TO_DATE(string||'00','YYYYMMDDHH24MI')
>
> Do this in your code, not on the source files. Seems to be pretty easy
> to me.
>
> > I have however looked at trying to change the format mask but can't
> > think of anything better. I've read that HH24 should handle the 24hr
> > clock (i was wondering if i had the right mask) but i can't see why it
> > refuses to handle the 00.
> >
>
> Well now you made me actually sign on to a database to do a simple test....
>
> HH24 is the 24hour clock designation for just the hours. I did not know
> if Oracle would assume the minutes to be '00' if not provided, so I did
> a simple test in Oracle 9.2.0.7 on Sun Solaris:
>
> SQL> select to_char(to_date('2006092117','YYYYMMDDHH24'),'YYYYMMDD:HH24MI')
> 2 from dual;
>
> TO_CHAR(TO_DA
> -------------
> 20060921:1700
>
> As you can see, I did not specify the minutes when I converted my string
> to a date. I then converted it back to a string, but asked it to display
> the minutes. You can see it displayed '00' for the minutes.
>
>

hmmm..........
maybe i should have been more clear.

currently, the '2006092117 string is only part of it. I am using to_date but i am also using other statements such as SUBSTR and INSTR to pull out parts of the overall string (eg of this: 2006092117_876_gted.log) i place 876 in a field and 2006092117 into a field. the format mask YYYYMMDDHH24 is giving me the date and time that i want. eg - 21/09/2006 17:00:00.
the problem is when the HH is 00 - i would have thought it would have given me the result 00:00:00. However, no time is being displayed. The date is but it is just missing out the time.

I did try placing ||'00' after the string and adjusting the format mask - but this was giving me the same results. As it gives me the minutes and seconds as default 00 values (this is determined in the regional settings of the local machine).

Im using TOAD and so was wondering if that was not showing the date properly for midnight.

If i have missed your point - im sorry (im a relative newbie at SQL!) Received on Thu Sep 21 2006 - 10:13:01 CDT