Check if hire_date is in format of (DD-MMM-YYYY)?

Question

I am currently working on a query where I have a table with columns

--------------------------------------------------------------------------------
EID(number)                ENAME(varchar2)                  HIRE_DATE(varchar2)
101                        PaulJones                        20120104
102                        DavidSmith                       27-JAN-1995
103                        BellaSwan                        15May2020 05:30:00

Expected Output :
EID        ENAME             HIRE_DATE           VALID_DATE
101        PaulJones         20120104               False
102        DavidSmith        27-JAN-1995            True
103        BellaSwan         15May2020 05:30:00     False

So David has true on the Valid_date column because his hire_date matches the default format of ORACLE date() function.

I was trying to use:

SELECT EID, ENAME, 
(CASE 
WHEN ISDATE(HIRE_DATE)=1 THEN 'TRUE'
ELSE 'FALSE'
END) AS VALID_DATE
FROM EMP_INFO2;

Error :

ISDATE() invalid identifier.

I think this function does not work in Oracle. Any other alternatives?

Actually In oracle, if you set any column say 'hiredate' with data type 'date'. It will take values for that column as '27-Jan-2018', not like '27-01-2018'. It shows error when I try that. — Akansha Kaushik, Jun 05 '18 at 13:30
Storing dates in a `varchar` columns is a **really**, really bad idea. If you had defined it as a `DATE` you wouldn't have the problem now — , Jun 05 '18 at 13:32
@AkanshaKaushik Oracle will NOT necessarily assume any particular format like `'27-Jan-2018'`, it will use the format you specify, or `nls_date_format` and `nls_date_language` (which themselves default from `nls_territory`) if you don't. If you use desktop tools like PL/SQL Developer or Toad, they have their own default settings (or they use the Windows desktop settings) which may be different from the database defaults. Never assume a date format, it could really be anything. — William Robertson, Jun 05 '18 at 14:06
I am not sure about all the different data types or the default data types format , oracle uses. I am new in this SQL thing. All I have experienced till now is , I am using SQL developer, if I define a column for eg. Birthdate as Date, when I am inserting rows in the table. For Birthdate it takes date in the format of '27-Jan-1995'. If I try 27/01/1995 or 27January1995. It will show error to me. — Akansha Kaushik, Jun 05 '18 at 20:03

score 1 · Answer 1 · answered Jun 05 '18 at 13:02

1

You can write your own isDate function (adapt the format to whatever you need)

CREATE OR REPLACE FUNCTION isDate( p_date_str IN VARCHAR2 )
  RETURN VARCHAR2
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_date_str, 'DD-MON-YYYY' );
  RETURN 'TRUE';
EXCEPTION
  WHEN others THEN
    RETURN 'FALSE';
END isDate;

And the your query is straightforward:

SELECT EID, ENAME, ISDATE(HIRE_DATE) AS VALID_DATE
FROM EMP_INFO2;

answered Jun 05 '18 at 13:02

StephaneM

4,779
1
16
33

hi thankyou for the answer. the function is working, but giving the wrong output. It should give true for DavidSmith. but false for all. Could you please explain this line for me " l_date := to_date( p_date_str, 'DD-MON-YYYY' );" from the function. ? – Akansha Kaushik Jun 05 '18 at 13:23
It tries to convert the string to a date using the format 'DD-MON-YYYY'. On success it goes to the next line and return true. If the input cannot be converted because it's not the right format it throws an exception which is catched and in this case we return 'FALSE' – StephaneM Jun 05 '18 at 13:37
Okay Got it. Thank you StephaneM – Akansha Kaushik Jun 05 '18 at 19:54
StephaneM thank you so much. It is working fine now .I am getting my desired output. – Akansha Kaushik Jun 05 '18 at 22:01

score 0 · Answer 2 · answered Jun 05 '18 at 13:30

Does the column hiredate have dates in more than one format? That would require more complicated code along the lines of

CREATE OR REPLACE FUNCTION isdate (pdate VARCHAR2) RETURN NUMBER 
IS
  FUNCTION try_format(pformat VARCHAR2) RETURN BOOLEAN IS
    l_date DATE;
  BEGIN
    l_date := TO_DATE(pdate, pformat);
    RETURN TRUE;
  EXCEPTION WHEN OTHERS THEN 
    RETURN FALSE;
  END try_format;
BEGIN 
  IF try_format('YYYYDDMM') 
  OR try_format('DD-MON-YYYY') 
  OR try_format('DDMONYYYY HH24:MI:SS')
     THEN RETURN 1; 
     ELSE RETURN 0; 
  END IF;
END isdate;
/

CREATE TABLE emp_info (eid NUMBER, hire_date VARCHAR2(30));
INSERT INTO  emp_info VALUES (101,'20120104');
INSERT INTO  emp_info VALUES (102,'27-JAN-1995');
INSERT INTO  emp_info VALUES (103,'15May2020 05:30:00');

SELECT eid, isdate(hire_date) AS valid_date FROM emp_info;

EID  VALID_DATE
101  1
102  1

But I'd recommend to either change the data type of column hire_date to DATE, or, if that's not possible, to at least add a new column for the valid_date. 103 1

Hello , Thank you sir. but this is not working for me. Shows a error : ORA-06575: Package or function ISDATE is in an invalid state 06575. 00000 - "Package or function %s is in an invalid state" *Cause: A SQL statement references a PL/SQL function that is in an invalid state. Oracle attempted to compile the function, but detected errors. *Action: Check the SQL statement and the PL/SQL function for syntax errors or incorrectly assigned, or missing, privileges for a referenced object. Error at Line: 20 Column: 13 — Akansha Kaushik, Jun 05 '18 at 20:18

Check if hire_date is in format of (DD-MMM-YYYY)?

2 Answers2