select convert(varchar(10),dateadd(dd,-DatePart(day,dateadd(dd,-1,getdate())),getdate()),120) GO DECLARE @Date DATETIME SET @Date=GETDATE() --年初,计算给定日期所在年的第一天 SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS 所在年的第一天GO SELECT DATEADD(DAY,-1,GETDATE())
select convert(varchar(10),dateadd(dd,-DatePart(day,dateadd(dd,-1,getdate())),getdate()),112) DECLARE @Date DATETIME SET @Date=GETDATE() --年初,计算给定日期所在年的第一天 SELECT convert(varchar(10),DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),112) AS 所在年的第一天SELECT convert(varchar(10),DATEADD(DAY,-1,GETDATE()),112)
SELECT @Date AS '目前时间' ,DATEADD(DD,-1,@Date) AS '前一天' ,DATEADD(DD,1,@Date) AS '后一天' /*月计算*/ ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算,保证日恒久为1号 ,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天,建议使用DATEADD而不要直接“-1” ,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)' ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天' ,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天' ,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天' ,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天' /*周计算*/ ,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关 ,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关 ,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推 ,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关 ,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关 ,DATENAME(WEEKDAY,@Date) AS '本日是周几' ,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日,2-星期一,3-星期二......7-星期六 /*年度计算*/ ,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初' ,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末' ,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初' ,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末' ,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初' ,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末' /*季度计算*/ ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初' ,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末' ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初' ,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末' ,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初' ,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
一周的第一天 select @@DATEFIRST一个月的第一天 select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一 select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时 SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 :本月第一天减2ms. SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天 SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) 本月的第一个星期一 去掉时分秒 DATEADD(day, DATEDIFF(day,0,getdate()), 0) 显示星期几 select datename(weekday,getdate()) 如何取得某个月的天数 SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) 判断是否闰年: SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end 一个季度多少天 declare @m tinyint,@time smalldatetime select @m=month(getdate()) select @m=case when @m between 1 and 3 then 1 when @m between 4 and 6 then 4 when @m between 7 and 9 then 7 else 10 end select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01' select datediff(day,@time,dateadd(mm,3,@time))
GO
DECLARE @Date DATETIME
SET @Date=GETDATE()
--年初,计算给定日期所在年的第一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS 所在年的第一天GO
SELECT DATEADD(DAY,-1,GETDATE())
DECLARE @Date DATETIME
SET @Date=GETDATE()
--年初,计算给定日期所在年的第一天
SELECT convert(varchar(10),DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),112) AS 所在年的第一天SELECT convert(varchar(10),DATEADD(DAY,-1,GETDATE()),112)
--当年第一天
SELECT convert(varchar(10),DATEADD(yy,DATEDIFF(yy,0,getdate()),0),120)
/*
2012-01-01
*/
--当月第一天
SELECT convert(varchar(10),DATEADD(mm,DATEDIFF(mm,0,getdate()),0),120)
/*
2012-07-01
*/
--昨天
SELECT convert(varchar(10),DATEADD(d,-1,getdate()),120)
/*
2012-07-18
*/
select cast (dateadd(mm,datediff(mm,0,getdate()),0) as varchar(max))
--select convert(varchar(max), dateadd(mm,datediff(mm,0,getdate()),0))select dateadd(YYYY,datediff(YYYY,0,getdate()),0)
select cast (dateadd(YYYY,datediff(YYYY,0,getdate()),0) as varchar(max))select dateadd(DD,datediff(DD,0,getdate()),0)-1
select cast (dateadd(DD ,datediff(DD ,0,getdate()),0)-1 as varchar(max))
--本年的第一天
select CAST(DATEPART(year,GETDATE()) as nvarchar(100))+'-1'+'-1' '本年的第一天'--本月的第一天
select CAST (DATEPART(year,GETDATE()) as nvarchar(100))+'-'+CAST (DATEPART(month,GETDATE()) as nvarchar(100))+'-1' '本月的第一天'--当前日期的前一天
SELECT CAST (DATEPART(year, DATEADD(DAY,-1,GETDATE())) as nvarchar(100))+'-'+ CAST ( DATEPART(month, DATEADD(DAY,-1,GETDATE())) as nvarchar(100))+'-'+CAST ( DATEPART(DAY, DATEADD(DAY,-1,GETDATE())) as nvarchar(100))
'当前日期的前一天'
--向小F学习一周的第一天
select @@DATEFIRST一个月的第一天 select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一
select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 :本月第一天减2ms.
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) 本月的第一个星期一 去掉时分秒
DATEADD(day, DATEDIFF(day,0,getdate()), 0)
显示星期几
select datename(weekday,getdate())
如何取得某个月的天数
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) 判断是否闰年:
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))))
when 28 then '平年' else '闰年' end
一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))
SELECT CONVERT(NVARCHAR(10),DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),20) --一月的第一天
SELECT CONVERT(NVARCHAR(10),DATEADD(mm, DATEDIFF(mm,0,getdate()), 0),20) --昨天
SELECT CONVERT(NVARCHAR(10),DATEADD(dd, DATEDIFF(dd,0,getdate()), -1),20)
2、select convert(varchar(4),getdate(),121)+'-01-01'
3、select convert(varcahr(10),dateadd(day,-1,getdate()),121)
--定义给定的一天
DECLARE @Date DATETIME = GETDATE();
SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/*月计算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算,保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天,建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/*周计算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日,2-星期一,3-星期二......7-星期六
/*年度计算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度计算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
select @@DATEFIRST一个月的第一天 select dateadd(mm,datediff(mm,0,getdate()),0)本周的星期一
select dateadd(wk,datediff(wk,0,getdate()),0)一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) 当天的零时
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) 上个月的最后一天 :本月第一天减2ms.
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) 本月的最后一天
SELECT dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) 本月的第一个星期一 去掉时分秒
DATEADD(day, DATEDIFF(day,0,getdate()), 0)
显示星期几
select datename(weekday,getdate())
如何取得某个月的天数
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))) 判断是否闰年:
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))))
when 28 then '平年' else '闰年' end
一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))
SELECT convert(varchar(10),DATEADD(year,DATEDIFF(year,0,getdate()),0),120)
/*
2012-01-01
*/
--当月第一天
SELECT convert(varchar(10),DATEADD(month,DATEDIFF(month,0,getdate()),0),120)
/*
2012-07-01
*/
--昨天
SELECT convert(varchar(10),DATEADD(d,-1,getdate()),120)
/*
2012-07-18
本年的第一天:Select dateadd(year,datediff(year,0,getdate()),0)
本年的最后一天:select dateadd(day,-1,dateadd(year,datediff(year,0,getdate())+1,0))
上年的最后一天:select dateadd(day,-1,dateadd(year,datediff(year,0,getdate()),0))
本季度的第一天:Select dateadd(quarter,datediff(quarter,0,getdate()),0)
本季度的最后一天:Select dateadd(day,-1,dateadd(quarter,datediff(quarter,0,getdate())+1,0))
上季度的最后一天:Select dateadd(day,-1,dateadd(quarter,datediff(quarter,0,getdate()),0))
本月的第一天:Select dateadd(month,datediff(month,0,getdate()),0)
本月的最后一天:Select dateadd(day,-1,dateadd(month,datediff(month,0,getdate())+1,0))
上月的最后一天:Select dateadd(day,-1,dateadd(month,datediff(month,0,getdate()),0))
本周的第一天:Select dateadd(week,datediff(week,0,getdate()),0)
本周的最后一天:Select dateadd(day,-1,dateadd(week,datediff(week,0,getdate())+1,0))
上周的最后一天:Select dateadd(day,-1,dateadd(week,datediff(week,0,getdate()),0))