access与sql server05/08/12通用写法总结(2024更新)

最终整理已包含sql2005/2008/2012
最后更新于2024.2.4

首先说一下心得：
1、在处理数据库的时候一定要按标准的写法，否则在转库的时候会带来很大的问题
2、尽量不要用默认值，写入时传值，默认值在sql里占用一定的空间，而且在转换时会很麻烦，需要一个个设置默认值，要养成一个好习惯
3、在acc和sql并存情况下，主要避免时间字段上的问题

注意事项：
一、acc的是/否型和sql的bit型
    判断为真：where isok<>0      判断为假：where isok=0
    排序方法：access使用order by istj，sqlserver使用order by desc，通用写法：order by abs(istj) desc
    ps：isok=1可用在sql但不能用在acc中，acc用的是isok=-1或者isok (判断为假是 not isok,但不适用于sql)
    
二、access的文本型和sql的nvarchar型
    判断为空：where title='' or title is null
    判断非空：where title<>'' and title is not null
    ps:有时候数据库内部需要合并字串统一取出来，但遇到null内容时会报错，这时候就需要使用以下的方法转换
     access可以用 iif(name,name,'') ，意思是如果name的内容为null时，转换成空值
     mssql可以用 isnull(name,'') ，意思同上
    
三、时间方面
    1、查询或更新
    查询通用写法where regtime='"&now()&"'"   如果是acc也可以这个where regtime=#"&now()&"#"
    注意，时间一定要包含到外面去，内部时间acc用now()，而sql用getdate()
    2、datediff时间比较
     ACC：datediff('d',regtime,'"&now()&"')<10
     MSSQL(去掉了单引号)：datediff(d,regtime,'"&now()&"')<10
     注意：这里要重点说一下，按"小时"比较，在acc里是"h"，而在mssql里用hh，所以为了避开这个问题，尽量转成n(分钟)来处理
    3、between时间范围
     acc用#包含，mssql用'包含
     where addtime between #"&sj1&" 00:00:01# and #"&sj2&" 23:59:59#
     where addtime between '"&sj1&" 00:00:01' and '"&sj2&" 23:59:59'
   4、运算中时间的直接比较
     不能直接用这种，在sql中可能得不到预期结果 where '2019-11-5 01:01:00'>='2019-11-5 1:00:00'
     改进如下：
     where convert(datetime,'2019-11-5 01:01:00',120)>=convert(datetime,'2019-11-5 1:00:00',120)
   5、时间的加与减
      着重说一下2021.10遇到的新坑，比如时间减少2小时，在acc中可以直接相减，mssql不行
      ACC：addtime-2/24
      MSSQL：dateadd(n,-2*60,addtime)  这里用的是分钟，所以加了*60
      dateadd在acc中也是支持的，因此通用写法就是使用mssql中方式，此处在注意在acc中，n需要带上引号，这里用了分钟n，是因为使用小时的话，acc是h，mssql是hh，转成分钟更加通用
     
四、数值与字符型转换问题
      数值转字符：access用cstr，mssql中用convert(nvarchar(20),uid)
      字符转数值(首先要保证该列记录里的内容是纯数值的)：access用val(id)，mssql中用convert(int,id)


五、备注类型要通过cast(column as varchar)或convert(nvarchar(500),username)来使用


六、查询问题
    通常小数据使用like来查询,但在mssql中的ntext字段不能使用replace和like函数,通用的方法是用instr来判断
    access可以使用instr,但在mssql中不存在instr,需要改用charindex，注意instr和charindex的使用方法是相反的