sql server 常用脚本

------------------------------日期脚本--------------------------------------

 

DECLARE @CurrentDate DATETIME=GETDATE();

select
DATEADD(dd,0, DATEDIFF(dd,0,@CurrentDate)) AS 当前日期,
DATEADD(dd,-1, DATEDIFF(dd,0,@CurrentDate)) AS 昨天,
DATEADD(MM,DATEDIFF(MM,0,@CurrentDate),0) AS 本月第一日,
DATEADD(DAY,-1,DATEADD(MM,DATEDIFF(MM,0,@CurrentDate)+1,0)) 本月最后一日,
DATEADD(ms,-3,dateadd(mm, datediff(mm,0,getdate())+1, 0)) AS 本月的最后一天带时间,
DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,@CurrentDate)),0) AS 上月第一日,
DATEADD(d,-1,dateadd(m,-1,DATEADD(mm, DATEDIFF(m,0,@CurrentDate)+1, 0))) AS 上月最后一日,
DATEADD(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0)) AS 上月最后一日带时间

 

--当前日期：2017-01-20 00:00:00.000

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))
--当前日期：2017-01-20 23:59:59.000

SELECT DATEADD(SS,-1,DATEADD(DD,1,CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))))