Find dates that are not on the first or last day of the month

Question

I have a table with a name Locationsthat has a column with a name effective_datewith many dates from many years, and I want to get only those that are not on the first day of the month or on the last day of this month.

Answer 1

If the columns effective_dateare of type date, then this SQL query returns all rows with a non-zero value effective_dateother than the 1st or last day of the month:

select t.effective_date , count(*)
from dbo.foo t
where 1 = 1 -- just for clarity
  -- after the 1st day of the month
  and t.effective_date > dateadd(day                 ,
                           1-day( t.effective_date ) ,
                                  t.effective_date
                           )
  -- and prior to the last day of the month
  and t.effective_date < dateadd( day                                ,
                           -day( dateadd(month,1,t.effective_date) ) , 
                                 dateadd(month,1,t.effective_date)
                           )

If your column contains a time component with it, then there is any of:

• datetime
• smalldatetime
• datetime2
• datetimeoffset

You will want to close your databases and change the request, something like

select *
from dbo.foo t
where 1=1 -- added for clarity
  -- effective date on or after the 2nd of the month
  and t.effective_date >= convert(date,
                            dateadd(day                 ,
                              2-day( t.effective_date ) ,
                                     t.effective_date
                              )
                            )
  -- and prior to the last day of the month
  and t.effective_date < convert(date,
                           dateadd(day,
                             -day( dateadd(month,1,t.effective_date) ) ,
                                   dateadd(month,1,t.effective_date)
                             )
                           )

Answer 2

SQL Fiddle Demo .

:

CREATE TABLE Locations(
   effective_date DATETIME
)

INSERT INTO Locations
  VALUES('2014-01-01') -- First day so we would expect this NOT to be returned
INSERT INTO Locations
  VALUES('2014-01-02') -- This should be returned
INSERT INTO Locations
  VALUES('2014-01-31') -- Last day of January so this should NOT be returned

, , effective_date ​​ .

SELECT effective_date FROM Locations
 WHERE -- not the first day (the easy bit!)
   DATEPART(day, effective_date) <> 1 
       -- not the last day (slightly more complex)
   AND DATEPART(day, effective_date) <> 
       DATEPART(day, DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,effective_date)+1,0)))

January, 02 2014 00:00:00+0000 .

- , , :

DECLARE @sampleDate DATETIME
SET @sampleDate = '2014-01-02'

-- Calculate the number of months between '1900-01-01' and the @sampleDate
-- +1 as we want to shift into the following month so we can work back:
SELECT DATEDIFF(month,0,@sampleDate) + 1
-- Result --> 1369

-- Create a new date by adding the result of the previous step in
--  months to '1900-01-01'
SELECT DATEADD(month, DATEDIFF(month,0,@sampleDate)+1,0)
-- Result --> '2014-02-01' (giving first day of the following month)

-- Subtract one second from this
SELECT DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,@sampleDate)+1,0))
-- Result --> '2014-01-31 23:59:59' (giving the very end of the original month) 

-- Finally extract the day of the month
SELECT DATEPART(day, DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,@sampleDate)+1,0)))
-- Result --> 31 

Answer 3

1.

, :

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

: http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

Answer 4

T-SQL to display dates per month for the first and last day only. Also display dates in the month, excluding the first and last day.

declare @EndOfMonth  as DateTime 
set @EndOfMonth = (  SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) )
select @EndOfMonth as EndOfMonths

declare @EndOfMonthMinus1  as DateTime 
set @EndOfMonthMinus1 = (SELECT DATEAdd(DAY, -1, @EndOfMonth) )
select @EndOfMonthMinus1 as EndOfMonths

declare @BeginingOfMonth  as DateTime 
set @BeginingOfMonth  = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101))
select @BeginingOfMonth  as EndOfMonths  

declare @BeginingOfMonthPlus1  as DateTime 
set @BeginingOfMonthPlus1 = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-2),GETDATE()),101))
select @BeginingOfMonthPlus1 as EndOfMonths   

-- select dates in month exclude first and last day 
SELECT TOP 1000 [effective_date]
FROM [Locations]
where effective_date <= @EndOfMonthMinus1
  and effective_date >= @BeginingOfMonthPlus1  

-- select only first of month and last of month
SELECT TOP 1000 [effective_date]
FROM [Locations]
where [effective_date]<= @EndOfMonth
and [effective_date] >= @EndOfMonthMinus1
    and [effective_date] >= @BeginingOfMonth
    and [effective_date] <= @BeginingOfMonthPlus1

Hope this helps!