Convert string to Date

[GoTerps88]

I want to convert a field with a value of 'February 2008' to a date. How is this possible in Oracle. Thanks.

 

[SantaMufasa]

select to_date('February 2008','Month yyyy') from dual;

TO_DATE('
---------
01-FEB-08

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[GoTerps88]

Yes, thanks, but then when I try to make a comparision with an actual date it throws an error of invalid month.

I have a field in the database called reference_period.

SELECT *
FROM events
WHERE TO_DATE('February 2008','Month yyyy') > sysdate;

 

[GoTerps88]

SELECT *
FROM events
WHERE TO_DATE(reference_period,'Month yyyy') > sysdate;

 

[jimirvine]

Then the implication is that you have at least one entry in the column that has a mis-entered month.
Create the following function and test your column's values:

create or replace
function IsValidDate (p_date_string VARCHAR2, p_format_mask VARCHAR2)
RETURN VARCHAR2
IS
  notValid EXCEPTION;
  PRAGMA EXCEPTION_INIT ( notValid, -01843);
  l_x DATE;
BEGIN
  l_x:= TO_DATE(p_date_string, p_format_mask);
  RETURN 'Valid';
EXCEPTION
  WHEN notValid THEN
    RETURN 'Invalid';
END;

 

[GoTerps88]

Right I would have to filter the data with a subquery with valid month year reference periods in order to make this comparison in the WHERE clause.

 

[jimirvine]

I would be more inclined to fix the issue than try to work around it. i.e. identify the invalid rows. Update them so that they are valid. Then create a new DATE column to store the DATE values.

 

[SantaMufasa]

GoTerps,

Follow JimIrvine's wisdom.

Let bad data live a very short life.

Do not attempt to work around bad data...reject it the moment someone attempts to enter it/store it.


Also (as Jim mentions), create a DATE column in which to store your DATE data! I cannot emphasise enough that there is no good business or technical reason to store (in Oracle) DATE information in any data type besides DATE.

When you have read an implemented Jim's suggestions, he definitely deserves a

for his spot-on recommendations.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.