CODEWITHSUNDEEP
plsqlcomparedate
Johanna
Johanna

Reputation: 151

PL/SQL how to compare dates in proc

Dates in my DB are stored like this: "2/6/2009 6:30:00 PM". What is the proper way to compare this date to a date I'm passing in from my UI in a stored procedure?

Trying to do something like TE_ES_ACT_END_DATETIME < '2/6/2009 07:30:00', but this isn't working. Any suggestions? Thank you!

Upvotes: 1

Views: 4898

Answers (1)

GolezTrol
GolezTrol

Reputation: 116140

Use the TO_DATE function to translate a text to a date.

Like so:

to_date('2/6/2009 6:30:00 PM', 'MM/DD/YYYY HH12:MI:SS AM')

If you use the field name instead of the constant string, you will get the field value, translated to a date. You can compare it in your query, or return it and compare it in PL/SQL code using the normal > or < operators.

But of course, it is better to save the date as a date. :) If you like, you can do a step by step conversion:

First, add a date column to the table and write a trigger that updates the stringdate field whenever a date is entered.

  create or replace trigger DATESYNC before insert or update of YOURNEWDATEFIELD
  on YOURTABLE for each row
  begin
    :new.CURRENTSTRINGDATE := to_char(:new.YOURNEWDATEFIELD, 'MM/DD/YYYY HH12:MI:SS AM');
  end;

Then, edit the GUI that you use to enter a date in the date field instead of the string field. All other code will still make use of the string field and will keep working. Write an update statement that uses to_data to convert each text to a date.

Then, you can one by one eliminate all code that reads the string field and update it to use the date field. All code that is not converted yet will still work, because the trigger still updates the string field.

After all code is converted, drop the string field and the trigger.

That will make your code faster, your data more compact, and your queries will make better use of indexes on the date field.

Upvotes: 2

Related Questions