Re: TO_DATE ignores
Date: Thu, 21 Feb 2013 13:52:48 +0000
Message-ID: <CAOuMUT7aAyNz2O41FiA8X2V9yeexoBXjc4WxBsP6yX4aboLsoA_at_mail.gmail.com>
Hi Norman,
All is not lost. A cup of coffee sorted it out for me. If I convert the date back to a char using the same format string and compare it to the original char then I have a function that works.
FUNCTION f_isdate (p_date IN VARCHAR,p_format IN VARCHAR) RETURN VARCHAR2
DETERMINISTIC
IS
v_date DATE;
BEGIN
- This function attempts to convert the date supplied using the format supplied and returns 'Y'
- if the date is valid and 'N' if it is invalid.
- null is considered a valid date and return a 'Y'
SELECT TO_DATE(p_date, p_format) INTO v_date FROM dual; * if to_char(v_date,p_format) = p_date then* * RETURN 'Y';* * else* * RETURN 'N';* * end if;*
EXCEPTION
WHEN OTHERS THEN -- if we can't to_date the input, its not a date
RETURN 'N';
END f_isdate;
Regards,
Fergal
On 21 February 2013 13:46, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:
> Of course, whatever you have for the default date format
> (NLS_DATE_FORMAT) makes a difference.
>
> With 'dd/mm/yyyy', attempting to convert "01/03/2001" without any formt
> string works fine, but with 'DD-MON-YY' it fails with ORA-01843: not a
> valid month.
>
> If you use 2 character years, dd/mm/yy, then what does '01/02/03'
> represent when no format is supplied? Oracle do advise using 4 character
> years - but the default they provide is a YY. Hmmm.
>
>
> The SQL Reference Manual, in the section on Format Models, it says:
>
> "Oracle Database converts strings to dates with some flexibility. For
> example, when the TO_DATE function is used, a format model containing
> punctuation characters matches an input string lacking some or all of
> these characters, provided each numerical element in the input string
> contains the maximum allowed number of digits - for example, two digits
> '05' for 'MM' or four digits '2007' for 'YYYY'...".
>
> Followed by a couple of examples.
>
>
> http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#i34924
> - scroll down to below table 3-15 of datetime format elements.
>
>
>
> HTH
>
> Cheers,
> Norm.
>
> --
> Norman Dunbar
> Dunbar IT Consultants Ltd
>
> Registered address:
> Thorpe House
> 61 Richardshaw Lane
> Pudsey
> West Yorkshire
> United Kingdom
> LS28 7EL
>
> Company Number: 05132767
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Fergal Taheny Pentec IT Limited 2 knightsbrook court, Dublin Road, Trim, Co. Meath. +353 (0) 87 9823137 ftaheny_at_gmail.com This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify us immediately. It is possible for data transmitted by email to be deliberately or accidentally corrupted or intercepted. For this reason, where the communication is by email, Pentec IT does not accept any responsibility for any breach of confidence which may arise through the use of this medium. Pentec IT Limited is Registered in Ireland: No 443280 with a registered office at 2 Knightsbrook Court, Dublin Road, Trim, Co. Meath. Company Directors: Fergal Taheny, Caitriona Ni Riain. -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 21 2013 - 14:52:48 CET