PERL调用SQL语句，ERROR PRA-01861

609854

直接在oracle中执行成功：

1. select id from UA_SERVICE_DETAIL where end_time=to_date(sysdate,'yyyy-mm-dd');

复制代码

用PERL调用的时候报错：

1. my $sql = qq{SELECT ID,RESULT,END_TIME FROM  UA_SERVICE_DETAIL where end_time=to_date(sysdate,'yyyy-mm-dd')};

复制代码

DBD::Oracle::st fetch failed: ORA-01861: literal does not match format string (DBD ERROR: OCIStmtFetch) [for Statement "SELECT ID,RESULT,END_TIME FROM  UA_SERVICE_DETAIL where end_time=to_date(sysdate,'yyyy-mm-dd')"] at line 50.请高手帮忙，谢谢

Perl_Er

1. ORA-01861:
   
2.         literal does not match format string
   
3. Cause:         Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace.
   
4. Action:         Correct the format string to match the literal.

复制代码

http://ora-01861.ora-code.com/

crasshopper

to_date 改成 to_char 试试


SQL> select to_date(sysdate,'yyyy-mm-dd') from dual;
select to_date(sysdate,'yyyy-mm-dd') from dual
               *
? 1 ?????:
ORA-01861: literal does not match format string


SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

TO_CHAR(SY
----------
2012-05-09

609854

回复 2# Perl_Er


    这个好像是 PERL 中不支持 TO_DATE 这样的函数

609854

回复 3# crasshopper


    在oracle 中，是可以可以执行成功的， 通过PERL来查询的时候就报错了

Perl_Er

<<Programming.the.Perl.DBI>>

1. The following SQL expression can be used to convert an integer "seconds since 1-jan-1970" value to
   
2. the corresponding database date/time:
   
3. to_date(trunc(:unixtime/86400, 0) + 2440588, 'J') -- date part
   
4. +(mod(:unixtime,86400)/86400) -- time part
   
5. To do the reverse you can use:
   
6. (date_time_field - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400

复制代码

回复 4# 609854

1. TO_DATE
   
2. 
   
3. Convert an expression to a date value.
   
4. 
   
5. Syntax
   
6.       to_date(char[,'format'[,nls_lang])
   
7. 
   
8. Key
   
9.    char      String expression that will be converted to a date
   
10.    format    Date format to use.
    
11.    nls_lang  The international language to use.
    
12. to_date will convert either a character string or an expression into a date value.
    
13. 
    
14. The 'format' must be a valid DATE format: YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute
    
15. If no format is specified Oracle will assume the default date format has been supplied in char.
    
16. 
    
17. Examples
    
18. 
    
19. to_date('29-Oct-09', 'DD-Mon-YY')
    
20. to_date('10/29/09', 'MM/DD/YY')
    
21. to_date('120109', 'MMDDYY')
    
22. to_date('29-Oct-09', 'DD-Mon-YY HH:MI:SS')
    
23. to_date('Oct/29/09', 'Mon/DD/YY HH:MI:SS')
    
24. to_date('October.29.2009', 'Month.DD.YYYY HH:MI:SS')
    
25. 
    
26. SQL> select * from sales where order_date > to_date('29-Oct-09', 'DD-Mon-YY');
    
27. To check that year 2000 dates are appearing correctly try the following:
    
28. 
    
29. SELECT
    
30.      to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),1 * 12),'DD-MON-YYYY') y1999,
    
31.      to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),2 * 12),'DD-MON-YYYY') y2000,
    
32.      to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),7 * 12),'DD-MON-YYYY') y2005,
    
33.      to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),52 * 12),'DD-MON-YYYY') y2050
    
34. FROM   
    
35. DUAL;
    
36. 
    
37. -- Expected output
    
38. 
    
39. -- Y1999       Y2000       Y2005       Y2050
    
40. -- ----------- ----------- ----------- -----------
    
41. -- 01-JAN-1999 01-JAN-2000 01-JAN-2005 01-JAN-2050
    
42. Related

复制代码

Correct the format string to match the literal.

1. SQL> select to_date(sysdate,'yyyy-mm-dd') from dual;
   
2. select to_date(sysdate,'yyyy-mm-dd') from dual
   
3.                *
   
4. ERROR at line 1:
   
5. ORA-01830: date format picture ends before converting entire input string
   
6. 
   
7. 
   
8. SQL> select to_date(sysdate,'mm-dd-yyyy') from dual;  --这里的格式改了一下
   
9. 
   
10. TO_DATE(SY
    
11. ----------
    
12. 05/09/2012
    

复制代码

py

1. $dbh->do("ALTER session SET nls_date_format='YYYY-MM-DD HH24:MI:SS'");

复制代码

我以前这样做，后来干脆把ORACLE的默认日期格式改成我需要的样子了

vallygirl

select id from UA_SERVICE_DETAIL where trunc(end_time)=trunc(sysdate);

609854

回复 6# Perl_Er


  哥啊, 我一会看看你的代码:wink: , 我昨天最后都改成用:

1. ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);  
   
2.         #构造2011-01-05DATA格式
   
3.         $year=$year+1900;
   
4.         $mon = sprintf("%02d",$mon+1);
   
5.         $mday = sprintf("%02d",$mday);
   
6.         $joinTime = join('-',$year,$mon,$mday);

复制代码

1. ... where end_time=\'$joinTime\'};

复制代码