Problem with date comparision in Pro*C

Applications Programming
1

Hi,

I have written the following Pro*C program to get the difference between 2 dates.But when I am printing the value of the date difference,it is always showing 0.

#include<stdio.h>
#include<sqlca.h>
#include<oraca.h>
void main()
{
  EXEC SQL BEGIN DECLARE SECTION;
  int diff;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL select trim(to_date('20101003','yyyymmdd')-to_Date('20091003','yyyymmdd')) into :diff from dual;
  printf("\n The difference value is %d \n",diff);
}

But I need to get the exact date difference.Please help me to resolve.

Thanks in advance.

2

use to_date('2010-01-03' , 'YYYY-MM-DD') and to_date('2009-01-03' , 'YYYY-MM-DD')

Best practice: always use an indicator variable and check sqlca

  EXEC SQL BEGIN DECLARE SECTION;
       int diff=0;
       short ind=0;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL
    select whatever 
       into :diff :ind
    from mytable;
  if(sqlca.sqlcode < 0)
  {
        EXEC SQL ROLLBACK WORK RELEASE;
        fprintf(stderr, "Line: %d:  ORA-%05d\n", __LINE__, -1*sqlca.sqlcode);
        exit(1);
  }

I'm also surprised your code compiles with #include <sqlca.h> all of the oracle includes should be like this: #include "[some file].h"

3

Hi,

I printed the sqlca.sqlcode and it is returning 1012 error code.
What dose it mean?

4

at the unix prompt:

oerr ora 1012

gives you a succint version of the error and problem

You have to use EXEC SQL connect :usr identified by :pwd;
where usr= username pwd = password, both are char array variables.

5

Hi

Thanks for your reply.When I am using this code in my application,Its giving 1830 error.

This is the query I am using:

SELECT to_char(trim(to_date(:sEventStartTime,'YYYYMMDD')- to_date(:sCurrentDateTime,'YYYYMMDD')))
INTO :sDatediff :idatediff_i
FROM dual;

where sDatediff is declared as string.