current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a function to delete those records withina given time period but I get an error while executing it.

10:58:23  [EXPLAIN - 0 row(s), 0.000 secs]  [Error Code: 0, SQL State: 42883]  ERROR: operator does not exist: date < integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Where: PL/pgSQL function delyearlyrecords(text) line 9 at EXECUTE statement
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

please find below the function

Create Or Replace Function DelYearlyRecords (tablename TEXT) Returns integer as $$
  Declare
   Start_Date date;
   End_Date date;

      Begin
      Start_Date = (current_date - interval '13 months')::date;
      End_Date   = current_date;
    execute 'delete from ' ||tablename||
' where cast(date_dimension_year || ''-'' || date_dimension_month || ''-''||date_dimension_day as date) 
not between  '|| Start_Date ||'  and  '|| End_Date || ' ';

        RETURN 1;
       END;
$$ LANGUAGE plpgsql;

Thanks & Regards, Rajeev

share|improve this question
    
Why aren't you using a real DATE column? –  a_horse_with_no_name 9 mins ago
add comment

2 Answers

up vote 1 down vote

In SQL, date constants are surrounded by single quotes. You need for these to be in the string. You can do this by including a double single quote in the string:

   execute 'delete from ' ||tablename||
' where cast(date_dimension_year || ''-'' || date_dimension_month || ''-''||date_dimension_day as date) 
not between  '''|| Start_Date ||'''  and  '''|| End_Date || ''' ';
share|improve this answer
add comment
up vote 1 down vote

This is a horrible way to store dates in a table. Store them in the table as a date not as year, month and day integers.

What's happening is that 

date_dimension_year || ''-'' || date_dimension_month || ''-''||date_dimension_day

evaluates to something like 2012-04-01. Which is a mathematical expression on integers, producing a result like 2007.

What you really wanted was to prepend and append '', so you get '2012-04-01', which can be evaluated as date.

Better still, though, is to construct the date with intervals:

date_dimension_year * INTERVAL '1' YEAR + 
date_dimension_month * INTERVAL '1' MONTH + 
date_dimension_day * INTERVAL '1' DAY

... and then go fix your schema so you store the whole thing as date not a set of 3 integers in the first place so you don't have to jump through all these hoops.

share|improve this answer
    
I do not have control over the way data is stored I dint have any issues while running query as such but only when I used variables to store the values and then call it in the query later on did i get this error. –  user3393089 25 mins ago
    
I was running it thhis way execute ' delete from '||tablename|| ' where now() - cast(date_dimension_year||''-''||date_dimension_month||''-''||date_dimension_day AS date) > INTERVAL ''13 months''' ; –  user3393089 25 mins ago
    
I noticed that if i do the mathematical calculations and save it in variable instead of at the time of execution I was able to save a lot of time and so i was doing that. –  user3393089 23 mins ago
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.