CODEWITHSUNDEEP
sql-server
Tejas
Tejas

Reputation: 461

How to get count of number of days left in month from given date

I want the count of number of days from 25/02/2019 in month of February and expected result is 4

I tried using master..spt_values in sql server but did not get expected result

declare @fdays int ,@d date=cast('20190201' as date),@JoinDate date=cast('20190225' as date)
select count(dateadd(dd,number,@d)) from master..spt_values
where type = 'p'
and month(dateadd(dd,number,@d))=month(@d)
and year(dateadd(dd,number,@d))=year(@d)    
and cast(GETDate() as date)>= Cast(dateadd(dd,number,@JoinDate) as date )

The result of above code is 28 but I want 4

Please help me to find the expected result

Upvotes: 0

Views: 87

Answers (3)

Zorkolot
Zorkolot

Reputation: 2027

Starting with SQL Server 2012, you could just use the EOMONTH function:

SELECT DATEDIFF(DAY, '20190225', EOMONTH ('20190225')) + 1 [thedays]

= 4.

Upvotes: 0

iamdave
iamdave

Reputation: 12243

This is simple date arithmetic, you do not need to use spt_values:

declare @d date = '20190225';

select datediff(month,0,@d) as MonthsDiff   -- Months since an arbitrary date
      ,dateadd(month,datediff(month,0,@d)+1,0) as StartOfFollowingMonth -- Add months above +1 to same arbitrary date
      ,datediff(day,@d,dateadd(month,datediff(month,0,@d)+1,0)) as DaysBetweenGivenDate -- DATEDIFF between given date and start of month from above;

Output:

+------------+-------------------------+----------------------+
| MonthsDiff |  StartOfFollowingMonth  | DaysBetweenGivenDate |
+------------+-------------------------+----------------------+
|       1429 | 2019-03-01 00:00:00.000 |                    4 |
+------------+-------------------------+----------------------+

Upvotes: 3

Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Try this:

 declare @date date='20140603'
    select datediff(day, @date, dateadd(month, 1, @date))-day(@date)

Upvotes: 0

Related Questions