test/sql/ScalarFunction/exp.test001.ksh

   1 echo Create table t1(f1 date, f2 date);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 echo select * from t1;
   9 ---------------------------------------------------------
  10         t1.f1   t1.f2
  11 ---------------------------------------------------------
  12         2001/1/1        2001/1/3
  13         2001/1/1        2001/2/1
  14         2001/1/1        2001/3/1
  15         2001/1/1        2002/1/1
  16         2001/1/1        2002/2/2
  17
  18 echo select * from t1 where date_diff(f2,f1)=2;
  19 ---------------------------------------------------------
  20         t1.f1   t1.f2
  21 ---------------------------------------------------------
  22         2001/1/1        2001/1/3
  23
  24 echo select * from t1 where date_diff(f1,f2)=2;
  25 ---------------------------------------------------------
  26         t1.f1   t1.f2
  27 ---------------------------------------------------------
  28
  29 echo select * from t1 where date_diff(f2,f1)=31;
  30 ---------------------------------------------------------
  31         t1.f1   t1.f2
  32 ---------------------------------------------------------
  33         2001/1/1        2001/2/1
  34
  35 echo select * from t1 where date_diff(f2,f1)=59;
  36 ---------------------------------------------------------
  37         t1.f1   t1.f2
  38 ---------------------------------------------------------
  39         2001/1/1        2001/3/1
  40
  41 echo select * from t1 where date_diff(f2,f1)=365;
  42 ---------------------------------------------------------
  43         t1.f1   t1.f2
  44 ---------------------------------------------------------
  45         2001/1/1        2002/1/1
  46
  47 echo select * from t1 where date_diff(f2,f1)=397;
  48 ---------------------------------------------------------
  49         t1.f1   t1.f2
  50 ---------------------------------------------------------
  51         2001/1/1        2002/2/2
  52
  53 echo select * from t1 where date_diff(f2,f1)<59;
  54 ---------------------------------------------------------
  55         t1.f1   t1.f2
  56 ---------------------------------------------------------
  57         2001/1/1        2001/1/3
  58         2001/1/1        2001/2/1
  59
  60 echo select * from t1 where date_diff(f2,f1)<=59;
  61 ---------------------------------------------------------
  62         t1.f1   t1.f2
  63 ---------------------------------------------------------
  64         2001/1/1        2001/1/3
  65         2001/1/1        2001/2/1
  66         2001/1/1        2001/3/1
  67
  68 echo select * from t1 where date_diff(f2,f1)>59;
  69 ---------------------------------------------------------
  70         t1.f1   t1.f2
  71 ---------------------------------------------------------
  72         2001/1/1        2002/1/1
  73         2001/1/1        2002/2/2
  74
  75 echo select * from t1 where date_diff(f2,f1)>=59;
  76 ---------------------------------------------------------
  77         t1.f1   t1.f2
  78 ---------------------------------------------------------
  79         2001/1/1        2001/3/1
  80         2001/1/1        2002/1/1
  81         2001/1/1        2002/2/2
  82
  83 echo select * from t1 where date_diff(f2,f1)!=59;
  84 ---------------------------------------------------------
  85         t1.f1   t1.f2
  86 ---------------------------------------------------------
  87         2001/1/1        2001/1/3
  88         2001/1/1        2001/2/1
  89         2001/1/1        2002/1/1
  90         2001/1/1        2002/2/2
  91
  92 echo select * from t1 where date_diff(f2,'2001-01-01')=2;
  93 ---------------------------------------------------------
  94         t1.f1   t1.f2
  95 ---------------------------------------------------------
  96         2001/1/1        2001/1/3
  97
  98 echo select * from t1 where date_diff(f2,'2001-01-01')>=59;
  99 ---------------------------------------------------------
 100         t1.f1   t1.f2
 101 ---------------------------------------------------------
 102         2001/1/1        2001/3/1
 103         2001/1/1        2002/1/1
 104         2001/1/1        2002/2/2
 105
 106 echo select * from t1 where date_diff(f2,'2001-01-01')!=365;
 107 ---------------------------------------------------------
 108         t1.f1   t1.f2
 109 ---------------------------------------------------------
 110         2001/1/1        2001/1/3
 111         2001/1/1        2001/2/1
 112         2001/1/1        2001/3/1
 113         2001/1/1        2002/2/2
 114
 115 echo select * from t1 where datediff('2001-01-02','2001-01-01')=1;
 116 ---------------------------------------------------------
 117         t1.f1   t1.f2
 118 ---------------------------------------------------------
 119         2001/1/1        2001/1/3
 120         2001/1/1        2001/2/1
 121         2001/1/1        2001/3/1
 122         2001/1/1        2002/1/1
 123         2001/1/1        2002/2/2
 124
 125 Statement Executed