What did I do wrong in a PRO*C? Waiting for help!!!

显示全部楼层 · 2005-12-19 06:56:00

Hi,
I wanted to add one day into a date and it returned 1 day plus 15 years.
For example:
add one day to 11-DEC-2005, it return 12-DEC-2020. Please help. I am waiting for the solution. Thank you very much in advance.
Below is my Function:
*********************************************************
static void add_one(void)
{
memset(new_eff_date,0,sizeof(new_eff_date));
strcpy(new_eff_date,"&quot

;
EXEC SQL DECLARE add_one_cursor CURSOR FOR
SELECT TO_CHAR(TO_DATE(:cur_nbrjobs_effective_date)+ 1,'DD-MON-RRRR') or use format ("DD-MON-YYYY')
FROM DUAL;
EXEC SQL OPEN add_one_cursor;
POSTORA;
EXEC SQL FETCH add_one_cursor INTO
:new_eff_date:Ind_01;
POSTORA;
if ( NO_ROWS_FOUND )
printf("\nthe new eff date error.&quot

;
EXEC SQL CLOSE add_one_cursor;
POSTORA;
}

千问 · 2005-12-19 06:56:00

Gives an example to explain
1.select add_months(TO_DATE('2005-12-11','yyyy-mm-dd'),15*12) from dual
return 2020-12-11
2.select TO_DATE('2020-12-11','yyyy-mm-dd')+1 from dual
return 2020-12-12

千问 · 2005-12-19 06:56:00

Thank you for your response. I m sorry, I did not get it. What is your point?
Thanks.
if I use the following statement:
SELECT TO_CHAR(TO_DATE(:cur_nbrjobs_effective_date,'YYYY-MM-DD')+ 1,'DD-MON-YYYY')
FROM DUAL;
It returned '21-DEC-0011'
if I use below format:
SELECT TO_CHAR(TO_DATE(:cur_nbrjobs_effective_date,'YYYY-MM-DD')+ 1,'YYYY-MM-DD')
FROM DUAL;
It returned '0011-12-21'
If I EXEC from sqlplus, all formats are OK.

千问 · 2005-12-19 06:56:00

我用:
SELECT TO_CHAR(TO_DATE(:cur_nbrjobs_effective_date) + interval '1' day,'DD-MON-YYYY')
FROM DUAL;
get '12-DEC-2020' too.
我可看懂中文. 谢谢

千问 · 2005-12-19 06:56:00

另外 cur_nbrjobs_effective_date defined as char12. 它是从FETCH 而来
又及,
谢谢!

千问 · 2005-12-19 06:56:00

看的懂中文就好了
将变量cur_nbrjobs_effective_date的值贴出来，主要想看看它的格式是什么样的

千问 · 2005-12-19 06:56:00

cur_nbrjobs_effective_date = '11-DEC-2005' fm 'DD-MON-YYYY'
I think this is the problem. I am going to redefine this var to CHAR9 and it will be YYYYMMDD format. Any idea?
Thank you very much

千问 · 2005-12-19 06:56:00

All,
Please forgive me. I made a mistake because I was working long hours for last few days.
all my date vars set as char12, but I set char10 on the cur_nbrjobs_effective_date and it returned '11-DEC-20'.
That is why.
Thank you for all your concern.

千问 · 2005-12-19 06:56:00

最初由 TeachME 发布
[B]All,
Please forgive me. I made a mistake because I was working long hours for last few days.
all my date vars set as char12, but I set char10 on the cur_nbrjobs_effective_date and it returned '11-DEC-20'.
That is why.
Thank you for all your concern. [/B]
可以这样写一个小PRO*C程序试验一下：
cur_nbrjobs_effective_date 定义为char(11)＝'2005-12-11',然后用'YYYY-MM-DD'format，然后按照你的算法计算15年后加一天，看是否能得到正确的结果'2020-12-12'。正常的情况，是应该能得到这样的结果的。
由于实际程序中你的cur_nbrjobs_effective_date是fetch出来的，我不清楚对应的数据库字段是varchar2类型还是datel类型。然后“SELECT TO_CHAR(TO_DATE(:cur_nbrjobs_effective_date)+ 1,'DD-MON-RRRR') or use format ("DD-MON-YYYY')
FROM DUAL;”来看，建议你查看一下你数据库的日期设置类型。
呵呵，目前我也只能想到这些，毕竟我们的环境不一样。