access与SqlServer 之时间与日期及其它SQL语句比较
1、Datediff:
1.1算出日期差:
1.access: datediff('d',fixdate,getdate())
2.sqlserver: datediff(day,fixdate,getdate())
ACCESS实例: select * from table where data=datediff('d',fixdate,getdate())
sqlserver实例: select * from table where data=datediff(day,fixdate,getdate())
1.2算出时间差:
1.access: datediff('h',fixdate,getdate())
2.sqlserver: datediff(Hour,'2004-12-10',getdate())
ACCESS实例: select DATEDIFF('h',HMD,getdate())
sqlserver实例: select datediff(Hour,'2004-12-10',getdate())
1.3算出月份差:
1.access: datediff('m',fixdate,getdate())
2.sqlserver: datediff(Month,'2004-12-10',getdate())
ACCESS实例: select DATEDIFF('m',HMD,getdate())
sqlserver实例: select datediff(Month,'2004-12-10',getdate())
----------------------------------------------------------------------------
2、日期变量
1.access: #"&data&"#
2.sqlserver: '"&data&"'
ACCESS实例: select * from table where data=#"&data&"#
sqlserver实例: select * from table where data='"&data&"'
----------------------------------------------------------------------------
3、是否
1.access: not finished
2.sqlserver: finished=0
ACCESS实例: select * from table where not finished
sqlserver实例: select * from table where finished=0
----------------------------------------------------------------------------
4、求余数
1.access: a mod b=100
2.sqlserver: a % b =100
ACCESS实例: select a mod b=100 from table where not finished
sqlserver实例: select a % b =100 from table where finished=0
-----------------------------------
相关文档:
如果原来的数据库可用,分离数据库后,只附加数据文件,不附加日志
如果不可用,只能慢慢等还原操作完成了
日志文件太大了,应该定期整理日志
--压缩日志及数据库文件大小
&n ......
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varch ......
新建表:
create table [表名]
(
[自动编号字段] int IDENTITY (1,1) PRIMARY KEY ,
[字段1] nVarChar(50) default '默认值' null ,
[字段2] ntext null ,
[字段3] datetime,
[字段4] money null ,
[字段5] int default 0,
[字段6] Decimal (12,4) default 0,
[字段7] image null ,
)
删除表:
Drop table [表 ......
create procedure DeleteWareHouse_StoreArea_SummaryByPUR
(@po_no nvarchar(100))
as
begin
declare @cacheTable table(wh_id int);--声明一个table类型的变量
insert @cacheTable select wh_id from aps_inventory_store_area where description=@po_no--向变量@cacheTable中添加结果集
--select * from @cac ......