Oracle数据库，时间类型字段，in多个时间值，报错： 返回
CodingLife 发布于2025/9/30
1. C# 代码：
var beginTimes = new List<DateTime>() { DateTime.Now, DateTime.Now.AddDays(1) };
var maxSorts = db.Queryable<OrderEntity>()
.Where(e => e.InpatientRegId == "ZYBH202500000029" && e.OrderType == 1 && beginTimes.Contains(e.BeginTime))
.Where(e => e.OrderStatus != 1 && e.OrderStatus != 4 && e.OrderStatus != 99)
.Select(e => new
{
e.BeginTime,
MaxSort = SqlFunc.AggregateMax(e.Sort),
})
.GroupBy(e => e.BeginTime)
.ToList();
2. 生成的SQL：
SELECT "BEGINTIME" AS "BEGINTIME" , MAX("SORT") AS "MAXSORT" FROM "CT_ORDER" WHERE ((( "INPATIENTREGID" = N'ZYBH202500000029' ) AND ( "ORDERTYPE" = 1 )) AND ("BEGINTIME" IN ('2025-09-30 10:47:56.161','2025-10-01 10:47:56.173')) ) AND ((( "ORDERSTATUS" <> 1 ) AND ( "ORDERSTATUS" <> 4 )) AND ( "ORDERSTATUS" <> 99 ))GROUP BY "BEGINTIME"
3. 报错提示：ORA-01843: 无效的月份
百度查询了下，说： IN ('2025-09-30 10:47:56.161','2025-10-01 10:47:56.173') 这个里面的字符串，需要转时间，不能直接IN (字符串)，测试了下，使用转时间之后，确实不报错了
热忱回答（4）
-
fate sta VIP0
2025/9/30//参数化in ，特殊类型适合用这个兼容性好SqlFunc.ContainsArrayUseSqlParameters(object[] thisValue,stringparameterValue)0 回复 -
fate sta VIP0
2025/9/30
时间类型用这个吧
0 回复 -
CodingLife VIP0
2025/9/30
好的谢谢，我测试下...再给您回复
0 回复 -
CodingLife VIP0
2025/9/30
验证测试，生成以下SQL：
SELECT "BEGINTIME" AS "BEGINTIME" , MAX("SORT") AS "MAXSORT" FROM "CT_ORDER" WHERE ((( "INPATIENTREGID" = N'ZYBH202500000029' ) AND ( "ORDERTYPE" = 1 )) AND ("BEGINTIME" IN (to_timestamp('2025-09-30 13:50:27.600598', 'YYYY-MM-DD HH24:MI:SS.FF') ,to_timestamp('2025-10-01 13:50:27.609622', 'YYYY-MM-DD HH24:MI:SS.FF') )) ) AND ((( "ORDERSTATUS" <> 1 ) AND ( "ORDERSTATUS" <> 4 )) AND ( "ORDERSTATUS" <> 99 ))GROUP BY "BEGINTIME"
能够正常执行，不报错,谢谢老师...
0 回复