1 2 Previous Next 22 Replies Latest reply on Oct 8, 2016 9:37 AM by Etbin

    ISDATE function oracle

    sgudipudi

      Hi,

      I have seen the documetation about ISDATE funtion but i can't call this function in oracle.

       

      My DB version
      Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
      PL/SQL Release 11.2.0.4.0 - Production
      "CORE 11.2.0.4.0 Production"
      TNS for Linux: Version 11.2.0.4.0 - Production
      NLSRTL Version 11.2.0.4.0 - Production
      
      
      select isdate('20161005') from dual;
      
      ORA-00904: "ISDATE": invalid identifier
      00904. 00000 -  "%s: invalid identifier"
      *Cause:    
      *Action:
      Error at Line: 59 Column: 8
      

       

      https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1106.htm

       

      Can someone throw light on this?

       

      Thanks!

        • 2. Re: ISDATE function oracle
          John Thorton

          Sreenivas Gudipudi wrote:

           

          Hi,

          I have seen the documetation about ISDATE funtion but i can't call this function in oracle.

           

          1. MyDBversion
          2. OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction
          3. PL/SQLRelease11.2.0.4.0-Production
          4. "CORE11.2.0.4.0Production"
          5. TNSforLinux:Version11.2.0.4.0-Production
          6. NLSRTLVersion11.2.0.4.0-Production
          7. selectisdate('20161005')fromdual;
          8. ORA-00904:"ISDATE":invalididentifier
          9. 00904.00000-"%s:invalididentifier"
          10. *Cause:
          11. *Action:
          12. ErroratLine:59Column:8

           

          https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1106.htm

           

          Can someone throw light on this?

           

          Thanks!

          There is a (BIG) difference between plain SQL & PL/SQL.

          What is valid in one is not necessarily valid in the other.

          ISDATE returns BOOLEAN datatype which does NOT exist within plain SQL.

          ISDATE is only valid within PL/SQL procedure.

          • 3. Re: ISDATE function oracle
            Nimish Garg

            Write yours as [not tested]

             

            nimish@garg> CREATE OR REPLACE FUNCTION ISDATE (DVALUE IN VARCHAR2, DFORMAT IN VARCHAR2) RETURN NUMBER AS

              2      DUMMY DATE;

              3  BEGIN

              4      DUMMY:=TO_DATE(DVALUE,DFORMAT);

              5      RETURN (1);

              6  EXCEPTION

              7      WHEN OTHERS THEN

              8          RETURN (0);

              9  END;

            10  /

             

             

            Function created.

             

             

             

             

            nimish@garg> select isdate('20161005','yyyymmdd') from dual;

             

             

            ISDATE('20161005','YYYYMMDD')

            -----------------------------

                                        1

            • 4. Re: ISDATE function oracle
              AndrewSayer

              Does that documentation describe the system you are running? Have you tried the example exactly as it is written there?

               

              There is no isdate function as standard.

              Coming soon in 12.2 is a function that does pretty much what Nimish's does: http://docs.oracle.com/cloud/latest/exadataexpress-cloud/CSDBF/handling-data-errors.htm#CSDBF-GUID-DC485EEB-CB6D-42EF-97AA-4487884CB2CD

               

              Btw I struggle to see the value in a function like this if you don't tell it what format the date should be in.

              • 5. Re: ISDATE function oracle
                Chris Hunt

                Sreenivas Gudipudi wrote:

                 

                Hi,

                I have seen the documetation about ISDATE funtion but i can't call this function in oracle.

                You presumably didn't see the title of the documentation that you saw:

                OLAP DML Reference

                ISDATE is an OLAP DML function, not a SQL one.

                • 6. Re: ISDATE function oracle
                  William Robertson

                  It is not particularly clear if you land in the OLAP documentation from a Google search, but if you look at the 'About' and 'Getting started' pages for Oracle OLAP (which I've never used myself) it seems the commands you are looking at will only work within the Analytic Workspace Manager application. They certainly aren't regular SQL.

                  • 7. Re: ISDATE function oracle
                    sgudipudi

                    I see that but certainly don't know the difference between SQL and OLAP DML. My understanding is oracle engine is SAME for all the models(OLTP or OLAP), appears to me it's not. Thanks!

                    • 8. Re: ISDATE function oracle
                      sgudipudi

                      Thanks William, learned something new today " Analytic Workspace Manager application".

                      • 9. Re: ISDATE function oracle
                        mathguy

                        It appears to me that you think "Oracle engine" and "<...> application" are one and the same. They aren't. SQL*Plus works on top of Oracle database, and has commands like "COLUMN col FORMAT a20" - they work in SQL*Plus, but the Oracle engine has no clue what that means. Just because an application is able to interact with the database doesn't mean it's part of the "Oracle engine."

                        • 10. Re: ISDATE function oracle
                          sgudipudi

                          Thaks Nimish, Our java folks would like to store the date as number field to avoid the EST/UTC conversions. I want to validate the data before insrting, so would like call this function in check constraint on that  table. Looks like  only system defined functions works in constraints not user defined functions. I may have to write a trigger to validate it or constraint like below

                           

                          start_date_num number => YYYYMMDD

                           

                          ALTER TABLE TABLE_NAME ADD CONSTRAINT validate_date_ck CHECK(SUBSTR(start_date_num,5,2) <13  AND  SUBSTR(start_date_num,7,2) < 31);

                           

                          Thanks!

                          • 11. Re: ISDATE function oracle
                            Paul  Horth

                            Sreenivas Gudipudi wrote:

                             

                            Thaks Nimish, Our java folks would like to store the date as number field to avoid the EST/UTC conversions. I want to validate the data before insrting, so would like add this function to check constraint on table only system defined functions works in constraints not user defined. I may have to write a trigger to validate it or constraint like below

                             

                            start_date_num number => YYYYMMDD

                             

                            ALTER TABLE TABLE_NAME ADD CONSTRAINT validate_date_ck CHECK(SUBSTR(start_date_num,5,2) <13 AND SUBSTR(start_date_num,7,2) < 31);

                             

                            Thanks!

                            NEVER store a date as anything but a DATE (or timestamp or timestamp with time zone) column in the DB. What problem do your Java people think they've got.

                            • 12. Re: ISDATE function oracle
                              Frank Kulash

                              Hi,

                              Sreenivas Gudipudi wrote:

                               

                              Thaks Nimish, Our java folks would like to store the date as number field to avoid the EST/UTC conversions. ....

                              That's a really, really bad idea.

                              If you really can't do what you want with the DATE datatype, then use TIMESTAMP WITH TIME ZONE.

                              • 13. Re: ISDATE function oracle
                                CarlosDLG

                                Sreenivas Gudipudi wrote:

                                 

                                Thaks Nimish, Our java folks would like to store the date as number field to avoid the EST/UTC conversions....

                                Your Java folks are probably Java experts, but they are apparently totally lost about how things should be done on the database side.

                                 

                                Don't ignore the observations people have made about that idea.  You should convince them that it is a really bad idea.

                                 

                                Here's an article (not mine) you might want to read: Storing Date Values As Numbers (The Numbers)

                                • 14. Re: ISDATE function oracle

                                  Thaks Nimish, Our java folks would like to store the date as number field to avoid the EST/UTC conversions.

                                  Please explain how that makes any sense.

                                   

                                  What does the number 27568999 represent?

                                   

                                  Is that EST? UTC? How could you possibly know?

                                   

                                  A date is not a number unless you are talking about Java using unix epoch values to represent a date as the number of milliseconds since 1/1/1970.

                                   

                                  But those values won't accurately reflect daylight saving time or timezone differences.

                                   

                                  I suggest you first nail down ALL of the requirements for those 'dates' as regards:

                                   

                                  1. the user's client timezone/location when they provide/extract the value

                                  2. the session timezone when the value is used (inserted/extracted)

                                  3. the server timezone when the value is used

                                   

                                  All of those things can affect the value any particular client will see.

                                  1 2 Previous Next