CODEWITHSUNDEEP
sqlsql-server-2008
Ajendra Prasad
Ajendra Prasad

Reputation: 111

SQL query to find last day of current month?

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

Hi everyone I have a query to find the last day of current month, which is surely working well, but I am unable to understand it, because I have other similar requirements and have to change it accordingly.

Can somebody explain it to me.. Thanks in advance

Upvotes: 5

Views: 39109

Answers (4)

yoursMadhu
yoursMadhu

Reputation: 11

SELECT DATEPART (DD,EOMONTH(GETDATE())) AS 'the last day of the Current month'

  • Here DATEPART function is used to print the integer part of day.. if the the name of day is asked then we have to use DATENAME() function.

  • In query, last day means end of the month so we used EOMONTH() function.

  • GETDATE() is to represent current month.

Upvotes: 0

AlejandroR
AlejandroR

Reputation: 5201

CREATE FUNCTION  EOMONTH
(
    @date datetime,
    @months int
)
RETURNS datetime
AS
BEGIN
     declare @eom datetime
     declare @d datetime
     set @d = dateadd(MONTH, @months, @date)
     select @eom =   dateadd(SECOND,-1,DATEADD(MONTH,datediff(MONTH,0,@d)+1,0))
    RETURN  @eom 

END
GO

Upvotes: 0

FistOfFury
FistOfFury

Reputation: 7165

This will give you the last day of current month but ignores time

select EOMONTH(GETDATE())

From Microsoft tech net

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Get the DateTime of Now

GETDATE() -- 2011-09-15 13:45:00.923

Calculate the difference in month's from '1900-01-01'

DATEDIFF(m, 0, GETDATE()) -- 1340

Add the difference to '1900-01-01' plus one extra month

DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0) -- 2011-10-01 00:00:00.000

Remove one second

DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0)) -- 2011-09-30 23:59:59.000

Upvotes: 21

Related Questions