CODEWITHSUNDEEP
sqloracle-databasedateplsql
FatihCeng
FatihCeng

Reputation: 57

How can I check the format of my date?

I have a table and I want to check the datetime format of records according to YYYYMMDD,HH24MISS. If the datetime format of my records is incorrect, write an error message. How can I make a function or procedure in PL/SQL?

Upvotes: 0

Views: 123

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

You can write a function like this:

CREATE OR REPLACE FUNCTION CheckDateString(str IN VARCHAR2) RETURN DATE IS
BEGIN
    RETURN TO_DATE(str,'YYYYMMDD,HH24MISS');
EXCEPTION
    WHEN OTHERS THEN 
        RETURN NULL;
END CheckDateString;

and use it like this

SELECT *
FROM my_table
WHERE CheckDateString(DATE_STRING) IS NULL 
    AND DATE_STRING IS NOT NULL;

Of course the next action point would be to correct the wrong values and change the data type of this column to DATE, resp. TIMESTAMP.

Upvotes: 1

Emil Moise
Emil Moise

Reputation: 393

In case your column is VARCHAR2 and you need to check that the values inside it can be transformed to date using your desired format, this could be a solution:

declare
  v_foo_date date;
begin
  for r_date in (
    select date_col from my_table
  ) loop
      begin 
        v_foo_date := to_date(r_date.date_col, 'YYYYMMDD,HH24MİSS');
        exception when others then
           dbms_output.put_line('error in validating value ' || r_date.date_col);
      end;
  end loop;
end;

Upvotes: 1

CM2K
CM2K

Reputation: 883

If you would like to get the data which has the desired format, maybe you could do something like this: Otherwise I would go with Wernfried Domscheit's answer.

  1. Cast the date as datetime from your table

    select *, cast(yourDateColumn as datetime) as verifiedDate into #tempTable from yourTable

  2. Then check the data from yourTable against the data you have in #tempTable

    select * from yourTable yt inner join #tempTable tt on yt.yourDateColum = tt.verifiedDate and yt.ID = tt.ID

Upvotes: 0

Related Questions