一、自增序列：
DB2:
create table TestTable
(
recordindex integer identity generated as always,
stringDateTime varchar(50),
TrueDateTime timestamp
);
sqlserver:
create table TestTable
(
recordindex int identity,
stringDateTime varchar(50),
TrueDateTime datetime
)
二、记录筛选：
DB2:
strTemp = "select * from TestTable fetch first 5 rows only";
sqlserver:
strTemp = "select top 5 * from TestTable";
三、时间函数:
sqlserver:
strTmpDB2 = "DELETE 登录日志表 WHERE DATEDIFF(DAY,登录时间,getdate())>"+Days;
DB2:
strTmpDB2 = "DELETE 登录日志表 WHERE Date(登录时间) - Date(""+System.DateTime.Now.ToString()+"") >"+Days;
sqlserver:
strTmpDB2 +=" and Cast(办件表.受理日期 as datetime)>=""+searchInfo.BeginDate+"" ";
DB2:
strTmpDB2 +=" and Date(办件表.受理日期)>= Date(""+searchInfo.BeginDate+"")";
sqlserver:
strSQL[Lilosoft.CoSpace.Util.DBType.DB2] = "INSERT INTO 公文交换发送表(公文编号,接收单位,分发时间,发送状态) VALUES (@strCode,@strUnit,getdate(),@strMsg)";
DB2:
strSQL[Lilosoft.CoSpace.Util.DBType.DB2] = "INSERT INTO 公文交换发送表(公文编号,接收单位,分发时间,发送状态) VALUES (@strCode,@strUnit,timestamp(""+ System.DateTime.Now.ToString() +""),@strMsg)";
四、类型检查
sqlserver:
varchar类型与int类型之间可以进行隐式转换和连接计算
DB2:
varchar类型与int类型之间不能进行隐式转换和连接计算
修改 角色权限表 角色序号字段 字段类型为integer,原类型为varchar
修改 办件部门表 部门序号字段 字段类型为integer,原类型为varchar
五，自增返回
sqlserver:
"SELECT @@IDENTITY"
DB2:
"select max(序号) from TableA"
六，对象所有
sqlserver:
"select * from dbo.TableA"
DB2:
"select * from TableA"
七、类型检查
sqlserver:
bit 型能被DotNet默认转换为Bool型
DB2:
smallint型不能够被DotNet默认转换为Bool型
八、标准支持
sqlserver:
delete TableA where a = a
DB2:
delete from TableA where a = a
九、日期格式
办件表中受理日期字段类型为varchar(8),字段值形如:20060113
sqlserver:
"and Cast(办件表.受理日期 as datetime)>=""+strBeginDate+"" "
DB2:
"and Date(Concat(Concat(Concat(Concat(Substr(办件表.受理日期,1,4),"-"),Substr(办件表.受理日期,5,2)),"-"),Substr(办件表.受理日期,7,2)))<= Date(""+strBeginDate+"")"
