Return Just the Last Day of Each Month with SQL

Return just the last day of each month with SQL

SQL Server (other DBMS will work the same or very similarly):

SELECT
  *
FROM
  YourTable
WHERE
  DateField IN (
    SELECT   MAX(DateField)
    FROM     YourTable
    GROUP BY MONTH(DateField), YEAR(DateField)
  )

An index on DateField is helpful here.

PS: If your DateField contains time values, the above will give you the very last record of every month, not the last day's worth of records. In this case use a method to reduce a datetime to its date value before doing the comparison, for example this one.

Get the last day of the month in SQL

Here's my version. No string manipulation or casting required, just one call each to the DATEADD, YEAR and MONTH functions:

DECLARE @test DATETIME
SET @test = GETDATE()  -- or any other date

SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)

tsql: How to retrieve the last date of each month between given date range

You can use CTE for getting all last days of the month within the defined range

Declare @Start datetime
Declare @End datetime

Select @Start = '20130808'
Select @End = '20131111'
;With CTE as
(
Select @Start  as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
UNION ALL
Select Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE
Where Date<@End
)

Select * from CTE
where [Last]=1   OPTION ( MAXRECURSION 0 )

How to select last day of the month SQL if the are no entries for that month

This is an interesting variation on the "filling in the blanks" type of query. The difference is that you want to keep the original rows if they match, but create a new one for months that do not.

You can approach this by:

• Generating a range with one row per month.
• Cross joining to generate a row for each id and each month.
• Joining back to the original data to get any rows that already exist.

This looks like:

WITH months AS (
      SELECT DATEFROMPARTS(YEAR('20140518'), MONTH('20140518'), 1) as mon
      UNION ALL
      SELECT DATEADD(MONTH, 1, mon)
      FROM months
      WHERE mon < DATEFROMPARTS(YEAR('20140720'), MONTH('20140720'), 1)
    )
SELECT i.id,
       COALESCE(t.date, EOMONTH(m.mon)),
       COALESCE(t.frequency, 0)
FROM (SELECT DISTINCT ID FROM dbo.MyTable) i CROSS JOIN
     months m LEFT JOIN
     dbo.MyTable t
     ON t.id = i.id AND
        t.date >= m.mon AND
        t.date < DATEADD(month, 1, m.mon);

Note that you can generate the dates you want directly in a recursive CTE. There is no need to generate numbers first.

Get data from the last day of the month without the use of loops or variables

If you are actually just after the single most recent row for each month, there is no need for a while loop to achieve this. You just need to identify the max date value for each month and then filter your source data for those for those rows.

One way to achieve this is via a row_number window function:

declare @t table(id int,dt datetime2);
insert into @t values(1,getdate()-40),(2,getdate()-35),(3,getdate()-25),(4,getdate()-10),(5,getdate());

select id
      ,id_device
      ,dt
from(select id
           ,id_device
           ,dt
           ,row_number() over (partition by id_device, year(dt), month(dt) order by dt desc) as rn
     from @t
    ) as d
where rn = 1;

SQL Query to find the last day of the month

Try this one -

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
    @Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Query:

DECLARE @date DATETIME
SELECT @date = '2013-05-31 15:04:10.027'

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Output:

-----------------------
2013-05-31 00:00:00.000

SQL SELECT First Day and Last Day of the Month.

Convert @FirstDOM to DATE as below:

declare @FirstDOM datetime, @LastDOM datetime

set @FirstDOM = (select CONVERT(DATE,dateadd(dd,-(day(getdate())-1),getdate())) )
set @LastDOM = (select dateadd(s,-1,dateadd(mm,datediff(m,0,getdate())+1,0))) 
SELECT @FirstDOM,@LastDOM

I hope this will help!

Thanks,

Swapnil

Return just last day from all available rows for each month

SELECT * 
FROM (
      select  DateAndTime, TagIndex,Val
        ,ROW_NUMBER() OVER (PARTITION BY MONTH(DateAndTime) ORDER BY DateAndTime DESC) RN 
      FROM FloatTable 
      WHERE TagIndex = 325 
        AND Val > 0
     )A
WHERE RN = 1

Since you are using SQL Server 2012, you can make use of the new window functions like LAST_VALUE()
FIRST_VALUE().

Using FIRST_VALUE()

SELECT *
FROM (
    SELECT  DateAndTime, TagIndex,Val
    ,FIRST_VALUE(DateAndTime) OVER (PARTITION BY MONTH(DateAndTime) 
                                           ORDER BY DateAndTime DESC) Last_Date 
    FROM FloatTable 
    WHERE TagIndex = 325 
    AND Val > 0
    ) A
WHERE DateAndTime = Last_Date

Using LAST_VALUE()

SELECT *
FROM (
    SELECT  DateAndTime, TagIndex,Val
    ,LAST_VALUE(DateAndTime) OVER (PARTITION BY MONTH(DateAndTime) ORDER BY DateAndTime 
              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) Last_Date 
    FROM FloatTable 
    WHERE TagIndex = 325 
    AND Val > 0
    ) A
WHERE DateAndTime = Last_Date

SQL Query for retreiving records that fall on the last day of the month

Use the DateSerial Function to compute the last day of the month for a given date.

Passing zero as the third argument, day, actually returns the last date of the previous month.

rdate = #2013-7-24#
? DateSerial(Year(rdate), Month(rdate), 0)
6/30/2013 

So to get the last date from the rdate month, add 1 to the month argument.

? DateSerial(Year(rdate), Month(rdate) + 1, 0)
7/31/2013 

You might suspect that approach would break for a December rdate, since Month() + 1 would return 13. However, DateSerial still copes with it.

rdate = #2013-12-1#
? DateSerial(Year(rdate), Month(rdate) + 1, 0)
12/31/2013 

If you will be running your query from within an Access application session, you can build a VBA function based on that approach, and use the custom function in the query.

However, if the query will be run from an ODBC or OleDb connection to the Access db, the query can not use a VBA user-defined function. In that situation, you can use DateSerial directly in your query.

SELECT m.*
FROM mytable AS m
WHERE m.rdate = DateSerial(Year(m.rdate), Month(m.rdate) + 1, 0)

That should work if your rdate values all include midnight as the time component. If those values include other times, use DateValue.

WHERE DateValue(m.rdate) = DateSerial(Year(m.rdate), Month(m.rdate) + 1, 0)

---

Related Topics