Below code returns remaining days in the month based on the input date. We can have this inside a function also which will return the integer value.
/* code starts here */
/* calculate remaining days in the month from the given input */
declare @tdate datetime
set @tdate = '2011-02-10'
select (CAST(DATEPART(DD,@tdate) as varchar(4))+'-'+CAST(DATEPART(MM,@tdate) as varchar(4))+'-'+CAST(DATEPART(YYYY,@tdate) as varchar(4))) As Today
,case when DATEPART(MM,@tdate) in (1,3,5,7,8,10,12)
then (31-DATEPART(DD,@tdate))
when DATEPART(MM,@tdate) in (4,6,9,11)
then (30-DATEPART(DD,@tdate))
when (DATEPART(MM,@tdate) = 2) and ((DATEPART(YYYY,@tdate) % 4) = 0)
then (29-DATEPART(DD,@tdate))
else (28-DATEPART(DD,@tdate))
end as Month_Remaining_Days
Run the above code in the query editor. Sample output is:
Today Months_Remaining_Days
2011-02-10 18
Subscribe to:
Post Comments (Atom)
Above script is converted into scalar user defined function....
ReplyDelete/* Function for Calculating remaining days in the month from the given input */
create function udf_calculate_remaining_days(@tdate datetime)
returns integer
as
begin
declare @iresult integer
set @iresult =
--(CAST(DATEPART(DD,@tdate) as varchar(4))+'-'+CAST(DATEPART(MM,@tdate) as varchar(4))+'-'+CAST(DATEPART(YYYY,@tdate) as varchar(4))) As Today
case when DATEPART(MM,@tdate) in (1,3,5,7,8,10,12)
then (31-DATEPART(DD,@tdate))
when DATEPART(MM,@tdate) in (4,6,9,11)
then (30-DATEPART(DD,@tdate))
when (DATEPART(MM,@tdate) = 2) and ((DATEPART(YYYY,@tdate) % 4) = 0)
then (29-DATEPART(DD,@tdate))
else (28-DATEPART(DD,@tdate))
end
return @iresult
end
select dbo.udf_calculate_remaining_days('2012-02-10')