Re: TO_DATE ignores

From: Fergal Taheny <ftaheny_at_gmail.com>
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-l
Received on Thu Feb 21 2013 - 14:52:48 CET

Original text of this message