Few questions are always popular. They keep on coming up through email, comments or from co-workers. Finding Last Day of Any Month is similar question. I have received it many times and I enjoy answering it as well.
I have answered this question twice before here:
SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Query to Find First and Last Day of Current Month
Today, we will see the same solution again. Please use the method you find appropriate to your requirement.
Following script demonstrates the script to find last day of previous, current and next month.
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth
ResultSet:
LastDay_PreviousMonth
———————–
2007-07-31 23:59:59.000
LastDay_CurrentMonth
———————–
2007-08-31 23:59:59.000
LastDay_NextMonth
———————–
2007-09-30 23:59:59.000
If you want to find last day of month of any day specified use following script.
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth
———————–
2007-08-31 23:59:59.000
Reference : Pinal Dave (https://blog.sqlauthority.com)
289 Comments. Leave new
This works perfectly
CONVERT(VARCHAR(8),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),1)
but can someone explain WHY? Please :)
Hey Guys:
Would love some help on this. There is an “Accounting_Date” column in my table and I would like to create a query to reference this date returning the last day of the month. For example if the date column is 2010-01-13 00:00:00.000, I would like it to return 01/31/2010. Is there anyway to make that happen? Thanks in advance.
For those who want date only (ie without the time)use this
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS LastDayOfPrevMonth
or simply
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)-1 AS LastDayOfPrevMonth
Hello. Sir.. I want to get records like..
Month Count
Jan 012 10
Feb 22
march 2
from current month to last all months, current month is May and i want to get Jan, Feb,March, Apr, and May related records… Please help me…
Find out examples available here
How about to find last month (max) but the date is appear all.
For example in the database there are:
(year-month-day)
2012-04-01
2012-04-02
2012-04-03
2012-05-01
2012-05-02
2012-05-03
In this case, I just want 2012-05-01, 2012-05-02, and 2012-05-03 would appear (current month).
What is in query? Cause I tried >
SELECT CURRENTMONTH FROM DATE WHERE CURRENTMONTH = (SELECT MAX(CURRENTMONTH) FROM DATE)
Query result is only show the last month and last day > 2012-05-03.
Thanks.
While these queries do work, when I tried to use them within a CONVERT that converted the result to a varchar and used it as part of a where clause, it took over two minutes to execute against my database. I looked around and found another way of getting the last day of the month, as seen in this post , put that within the CONVERT and the query took less than a second to run. You may want to look at the other post if you are trying to get the last day of the month since it offers three different ways of getting the same result. Just more alternatives to consider.
Also look at the examples given at the end of this post
Hi,
Wondering how I can get 22nd Date of Previous month. eg if today is 25/06/2012 then last month 22nd Date should be 22/05/2012.
Thanks
select dateadd(day,22,getdate()-day(getdate()))
how to get next 6th months date from todays date
SELECT dateadd(month,6,getdate())
Plenty of good examples. Thanks
thanks dude; i was trying to write a sql script.
Thanks a lot mate.
How do I find the last 12 months from a given date including the current month. For example:
If the given date is 15-Jan-2012
It should return; jan 2012, dec 2011, nov 2011, oct 2011, sept 2011, aug 2011, july 2011, june 2011, may 2011, apr 2011, mar 2011, feb 2011
or if the given date is 31-Dec-2011
It should return; dec 2011, nov 2011, oct 2011, sept 2011, aug 2011, july 2011, june 2011, may 2011, apr 2011, mar 2011, feb 2011, jan 2011
I use this code to return the last friday in any month:
SELECT DATEADD(day, (DATEDIFF (day, ‘2010-01-01’, (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))) / 7) * 7, ‘2010-01-01’) AS “LAST_FRIDAY_OF_MONTH”
There’s a way shorter method in SQL Server 2012:
SELECT [MonthEnd] = EOMONTH()
Enjoy.
i need to get the tranaction details happend between current date and same date of prevoius month.how can i .pls help
Look at the examples posted at the bottom of this post and you will have an idea
Hi,
I am trying yo replicate the ‘n’th week and nth day of that week from the defined calender table. can anyone help me in writing query for the same, thanks..
Velmurugan
Awesome! Thanks a lot, this is what I was looking for.
-Abhi
Hi. Thanks for the post.
This returns 2012-10-31 23:59:59.000
How to make it to return 2012-10-31 00:00:00 000 ?
DECLARE @MyDate datetime
SET @MyDate=’15/03/1977′
DECLARE @Interval int
SET @Interval=0
SELECT DATEADD(day,-day(@MyDate),DATEADD(mm, 1+@Interval,@MyDate))
USE [TestDataBase]
GO
/****** Object: UserDefinedFunction [dbo].[LAST_DAY] Script Date: 11/08/2012 00:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Luis R. Vela Morales
— Create date: 01/11/2012
— Description: De una fecha dada, retorna la
— fecha con el ultimo dia del mes,
— esto decrementando, sin afectar o
— incrementando en ‘n’ meses
— ( -n, 0, n).
— =============================================
CREATE FUNCTION [dbo].[LAST_DAY]
(
— Add the parameters for the function here
@Fecha datetime,
@Intervalo int
)
RETURNS datetime
AS
BEGIN
— Declare the return variable here
DECLARE @Result datetime
— Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(day,-day(DATEADD(mm, 1+@Intervalo,@Fecha)),DATEADD(mm, 1+@Intervalo,@Fecha))
— Return the result of the function
RETURN @Result
END