CODEWITHSUNDEEP
sqlsql-serversql-server-2008
JonWay
JonWay

Reputation: 1735

Number of days left in current month

Number of days left in a given month How do I find the number of days left in the current month? Example if current month is November and todays date is 16/11/2016 The Numbers of days in month – Elapse days = ? I want to do it dynamically In my example 30 – 16 = 14

declare @date date 
set @date='16 Nov 2016'
select datediff(day, @date, dateadd(month, 1, @date)) - 16 AS DaysLeft

Upvotes: 2

Views: 6936

Answers (7)

eshchar meidan
eshchar meidan

Reputation: 1

FUNCTION example

CREATE Or ALTER FUNCTION ReturnDaysLeftInMonth(@Date Date)
RETURNS Int
AS
BEGIN
  RETURN DATEDIFF(DAY, @Date, EOMONTH(@Date)) + 1
END

Or use

Declare @Date Date 
Set @Date=GETDATE()
DATEDIFF(DAY, @Date, EOMONTH(@Date)) + 1

Upvotes: 0

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1726

--For SQL 2012 And Above Version Use Below Query to Get Count Of Days Left In A Month

DECLARE @date DATE
SET @date=GETDATE()
SELECT DATEDIFF(DAY, @date,EOMONTH(@date)) 

-- And for Sql Server 2008 Use Below Query to Get Count of Days Left for the Month
    DECLARE @date Date 
         SET @date=GETDATE()
         SELECT DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date)) - DATENAME(DAY,GETDATE())
     AS DaysLeft

Upvotes: 2

vinay koul
vinay koul

Reputation: 346

Simply use the Datepart function:

declare @date date
set @date='16 Nov 2016'
select datediff(day, @date, dateadd(month, 1, @date)) - Datepart(DAY,@date)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

Since this is sql server 2008 you can't use EOMonth (that was introduced in 2012 version).
You have to do some date adds and date diffs:

SELECT DATEDIFF(DAY, 
                GETDATE(),
                DATEADD(MONTH, 
                        1, 
                        DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()) 
                       ) 
               ) - 1

explanations:
DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()) gets the first day of the current month, the wrapping DATEADD adds one month, and the wrapping DATEDIFF returns the number of days between the current date and the first date of the next month. This is why you need to subtruct 1 to get the correct number of days.

Upvotes: 6

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Use EOMONTH and DATEDIFF functions 

    declare @date date 
    set @date='16 Nov 2016'
    select datediff(day,@date,eomonth(@date)) as days_left

Use below solution For Below versions of sql server 2012

    DECLARE @DATE DATE 
    SET @DATE='16 NOV 2016'
    SELECT   DATEDIFF(DAY,@DATE,DATEADD( MM,DATEDIFF(MM,0,@DATE)+1,0))-1 AS DAYS_LEFT

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

DECLARE @date DATE SET @date='16 Nov 2016' SELECT DATEDIFF(DAY, @date,EOMONTH(@date))

Upvotes: 1

PowerStar
PowerStar

Reputation: 895

Change your date to getdate()

     declare @date date 
     set @date=GETDATE()
     select datediff(day, @date, dateadd(month, 1, @date)) - DATENAME(DAY,GETDATE())
 AS DaysLeft

Upvotes: 1

Related Questions