SQL语句注意点

ROW_NUMBER序号

ROW_NUMBER() over(partition by id order by createdon) as rowNumber

临时表

select new_name into #tabletype from new_accounttype;

drop table #tabletype

case when

case when FPLPnrResult<>-1 then 1 else 0 end

参数判断

if (@invoicetypeid='安装费')
begin……end
else if (@invoicetypeid='维修费')
begin……end
else
begin……end

时间操作

1. 获取时间

select getdate() //2018-01-25 11:44:16.900
select DATEPART(month,getdate())//1
select DATEPART(day,getdate())//25
select DATEPART(dd,getdate())//25
select DateName (year,getdate())//2018
select DateName (month,getdate())//01
select DateName (weekday,getdate())//星期四
select DateName (dw,getdate())//星期四

2. 时间加减

select DATEADD(dd,20,getdate())//2018-02-14 11:45:43.553
select DATEADD(month,2,getdate())//2018-03-25 11:45:43.553
//day，year，month，day，hour，second

3. DATEDIFF() 返回两个日期之间的时间

select getdate() //2018-01-25 11:44:16.900
select DATEDIFF(MONTH,'2017-12-1',getdate())//1
select DATEDIFF(DAY,'2017-12-1',getdate())//55
select DATEDIFF(YEAR,'2017-12-1',getdate())//1

4. SQL转换日期格式

Select  CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 12): 060516

5. 判断时间

datediff(day,time1,time2) =0
//当time1 > time2 为负数;time1 < time2 为正数;

6. 参数 interval的设定值如下：