Search code examples
sqloracledatecomparisonvarchar

How sql compares date stored in varchar datatype

How a comparison between two dates are made if both are stored in varchar?

I came across a table which is written years ago in which the date (DD-MM-YY) is stored in a varchar format

updated_date varchar(255)

There's a script written for delete operation in following manner

(Temp variable)-

temp_date_tocompare varchar2(255) :=to_char(sysdate-7, 'YYYY-MM-DD')

delete from myTable
    where updated_date < temp_date_tocompare;

I executed this and it works fine.

But curious to understand behind the scenes work, how this varchars are compared.

Solution

  • Compare dates as dates. Store date/times using proper date/time formats. Do not store them as strings!

    Your code will no doubt delete rows. Whether it deletes the rows you want is another question. The logic you want seems to be:

    delete from myTable
    where to_date(updated_date, 'DD-MM-YY') < trunc(sysdate) - 7;