SQL Get the first occurance of the 15th for a date
I need to write a function or SP that will retur开发者_运维知识库n the first occurance of the 15th. For example, if I pass the date as May 8th, then it should return May 15th. If I pass May 30th, then it should return June 15th.
One way
DECLARE @d DATETIME
SELECT @d = '20110508'
--SELECT @d = '20110530'
SELECT CASE WHEN DAY(@d) > 15
THEN dateadd(mm, datediff(mm, 0, @d)+1, 0) + 14
ELSE dateadd(mm, datediff(mm, 0, @d)+0, 0)+ 14 end
How about;
create function udf_getNextDate(@base datetime, @day int) returns datetime as begin
set @base = case when day(@base) > @day
then dateadd(month, 1, @base)
else @base
end
return dateadd(day, -day(@base) + @day, @base)
end
select
dbo.udf_getNextDate('08 may 2011', 15),
dbo.udf_getNextDate('30 may 2011', 15),
dbo.udf_getNextDate('16 dec 2011', 15),
dbo.udf_getNextDate('01 may 2011', 15)
2011-05-15 00:00:00.000
2011-06-15 00:00:00.000
2012-01-15 00:00:00.000
2011-05-15 00:00:00.000
Just another way of doing it:
Declare @d datetime
Set @d = getdate()
Select Case
When DateDiff(Day, Day(@d), 15) < 0 then
DateAdd(month, 1, DateAdd(Day, DateDiff(Day, Day(@d), 15), @d))
Else DateAdd(Day, DateDiff(Day, Day(@d), 15), @d)
End as [Next15th]
this function may helps you
create function Get15th(@date datetime)
returns datetime
as
begin
declare @resultdate datetime
declare @y int
declare @m int
declare @d int
set @y = datepart(year,@date)
set @m = datepart(month,@date)
set @d = datepart(day,@date)
if( @d<=15)
set @resultdate =cast((str(@y)+'-'+str(@m)+'-15') as datetime)
else
set @resultdate =cast((str(@y)+'-'+str(@m+1)+'-15') as datetime)
return @resultdate
end
try
DATEADD(Day, DATEDIFF(Day, 15, Created), 0) AS CreatedDay
explained here http://improve.dk/archive/2006/12/13/sql-server-datetime-rounding-made-easy.aspx
using that link you can achive what you want
UPDATE: got it wrong ... see Dems answer
Rob's answer is close...
if you take the datediff in months, rather than days, and make your base the 15'th of a month, then add one extra month...
DATEADD(MONTH, DATEDIFF(MONTH, 14, Created) + 1, 14)
EDIT
Modified to use DATEDIFF MONTH how it works, not how I thought it should work ;)
DATEADD(MONTH, DATEDIFF(MONTH, 0, Created - 15) + 1, 14)
I usually do something like this:
declare
@date datetime ,
@target_day int
set @date = 'June 16, 2009'
set @target_day = 15
select date = @date ,
next_date = case when day(@date) <= @target_day
then dateadd(day,15-day(@date),@date)
else dateadd(day,15,dateadd(month,1,dateadd(day,-day(@date),@date)))
end
dealing with the last day of the month is a wee bit trickier, since month's have variable numbers of days, and SQL Server's date math is sometimes a wee bit baroque.
精彩评论