CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Domnic
Domnic

Reputation: 3867

Find no of days remaining on current month from given date

I just want to calculate the number of days remaining from given date

Ex:

if the date is '02/01/2016' then the remaining days on the given month is '28'

if 03/05/2016 then '25' days...

I tried the below

SELECT DATEPART(DD,'03/05/2016')  it given me only 5 days

Upvotes: 2

Views: 6792

Answers (9)

KirstieBallance
KirstieBallance

Reputation: 1358

With SQL 2012's new function EOMONTH() you could try:

DECLARE @Date date = '2017-08-08'
select datediff (dd, @Date, EOMONTH(@Date)) as DaysRemaining

Upvotes: 0

Pawel Czapski
Pawel Czapski

Reputation: 1864

check below,

DECLARE @date DATETIME = '02/05/2016'
SELECT CASE WHEN month(@date) IN (1,3,5,7,8,10,12) THEN 31 - DATEPART(DD,@date)
            WHEN month(@date) IN (4,6,9,11) THEN 30 - DATEPART(DD,@date)
            ELSE 28 - DATEPART(DD,@date)
            END

note: in above code leap year is not taken into account

edited: leap year proof version 

DECLARE @date DATETIME = '02/05/2016'
DECLARE @leap_year bit = 0

IF ((YEAR(@date) % 4 = 0 AND YEAR(@date) % 100 <> 0) OR YEAR(@date) % 400 = 0)
   SET @leap_year = 1

SELECT CASE WHEN MONTH(@date) IN (1,3,5,7,8,10,12) THEN 31 - DATEPART(DD,@date)
        WHEN MONTH(@date) IN (4,6,9,11) THEN 30 - DATEPART(DD,@date)
        WHEN MONTH(@date) = 2 AND @leap_year = 1 then 29 - DATEPART(DD,@date)
        ELSE 28 - DATEPART(DD,@date)
        END

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28938

SQLServer specific since you mentioned version as well: 

 select DATEDIFF(day,getdate(),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))

you just need to find last date of given month and calculating datediff is easy,Below expression does the same

  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

On 2012,it is much easier:

select datediff(day,getdate(),EOMONTH(GETDATE()))

Upvotes: 3

Wesam
Wesam

Reputation: 1070

you can use this one to calculate the diffreance from today 

SELECT DATEDIFF(day,getdate() ,EOMONTH(GETDATE()))

or this to calculate the date from a param

SELECT DATEDIFF(day,@date ,EOMONTH(GETDATE()))

Upvotes: 0

Cato
Cato

Reputation: 3701

DECLARE @MyDATE as datetime;


SELECT @MYDATE = '19960423';

--wind back to first of month
--add on one month
--now calculate days from original date, this is always '1 to high' so subtract 1

SELECT 
   DATEDIFF(day, @mydate, DATEADD(month, 1,D ATEADD(day, 1 - DAY(@MYDATE), @MYDATE))) - 1;

Upvotes: 0

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

This should do the Trick. hope it helps. My assumptions are that you are using SQL Server 2008. The trick here is to get the first day of the next month which will always be 01-NextMonth-Year

DATEADD(mm, 1, @Date)

and subtract 1 day from it to get the last day of current month

DATEADD(dd, - 1, CONVERT(VARCHAR(8), DATEADD(mm, 1, @Date), 121) + '01')

Now DateDiff between Input Date and Last day of the input month will give you remaining days in the month

DATEDIFF(dd, @Date, @LastDateofMonth)

DECLARE @Date DATE = GETDATE()
DECLARE @LastDateofMonth DATE

SELECT @LastDateofMonth = DATEADD(dd, - 1, CONVERT(VARCHAR(8), DATEADD(mm, 1, @Date), 121) + '01')

SELECT DATEDIFF(dd, @Date, @LastDateofMonth)

Upvotes: 0

Squirrel
Squirrel

Reputation: 24803

for SQL Server 

select DATEDIFF(DAY, 
                GETDATE(), 
                DATEADD(MONTH, 
                        DATEDIFF(MONTH, 0, GETDATE()) + 1, 
                        0)
               ) - 1

Upvotes: 1

Hitesh Thakor
Hitesh Thakor

Reputation: 471

select DATEDIFF(DD, GETDATE(),GETDATE())
select DATEDIFF(DD,'2015/10/05','2015/10/07')

Upvotes: -1

MT0
MT0

Reputation: 168681

Since you also tagged Oracle:

SELECT LAST_DAY( DATE '2016-02-01' ) - DATE '2016-02-01'
         AS days_remaining
FROM   DUAL;

Output:

DAYS_REMAINING
--------------
            28

Upvotes: 0

Related Questions