22,301
社区成员




select datediff(dd,getdate(),cast(convert(varchar(7),dateadd(mm,1,getdate()),120) + '-01' as datetime) - 1)
/*
-----------
3
(所影响的行数为 1 行)
*/
select datediff(dd,getdate(),cast(year(getdate())+1 as varchar)+'-01-01')
/*
-----------
65
(所影响的行数为 1 行)
*/
select cast(convert(varchar(7),dateadd(mm,1,getdate()),120) + '-01' as datetime) - 1
/*
------------------------------------------------------
2010-10-31 00:00:00.000
(所影响的行数为 1 行)
*/
select datediff(day, getdate()+1, ltrim((year(getdate())+1)))--64
if object_id('GetLeaveDays','fn') is not null
drop function GetLeaveDays
go
create function GetLeaveDays(@nowdate varchar(10))
returns int
as
--获取当前日期之后剩余天数
begin
declare @dates datetime
if len(@nowdate)>6
set @dates = cast(@nowdate as datetime)
else
set @dates = dateadd(d,-1,dateadd(m,1,cast(@nowdate+'01' as datetime)))
return 365 - datepart(dayofyear,@dates)
end
select dbo.GetLeaveDays('201010'),dbo.GetLeaveDays('20101001')
/*
----------- -----------
61 91
(1 行受影响)
*/
--datepart(dayofyear,date)
declare @s varchar(10)
declare @mindate datetime
set @s='20101028' --2010-10-28这一天
set @mindate=cast(cast(left(@s,4)+'0101' as varchar(10)) as datetime)
select 剩余天数=datepart(dayofyear,dateadd(day,-1,dateadd(year,1,@mindate)))-datepart(dayofyear,convert(datetime,@s)),
当年最小日期=@mindate, 当年最大日期=dateadd(day,-1,dateadd(year,1,@mindate)),
当前已过天数=datepart(dayofyear,convert(datetime,@s)),
当年最大天数=datepart(dayofyear,dateadd(day,-1,dateadd(year,1,@mindate)))
剩余天数 当年最小日期 当年最大日期 当前已过天数 当年最大天数
----------- ----------------------- ----------------------- ----------- -----------
64 2010-01-01 00:00:00.000 2010-12-31 00:00:00.000 301 365
(1 行受影响)