Re: to_date format

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 21 Sep 2006 13:57:06 GMT
Message-ID: <J5y43J.HE3@igsrsparc2.er.usgs.gov>

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.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Thu Sep 21 2006 - 08:57:06 CDT