Get last day of the month - 1

Question

Post reply

Get last day of the month - 1

DaveDB

SSCrazy

Points: 2173
More actions
October 17, 2013 at 1:56 pm

#284052

Hello all,
Have a quick question....
How can I get the last day of the month - 1? So if the last day of the month is 10/31/2013, I would need to see 10/30/2013? Also, I do not want time added to the date and I am using SQL 2005.
Here is what I have...
select Dateadd(d,-DATEPART(d,DateAdd(m,1,getdate()-1)),DateAdd(m,1,getdate()-1))
Any help would be appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jonysuise Ten Centuries Points: 1206 More actions · Answer 1

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

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

DaveDB SSCrazy Points: 2173 More actions · Answer 2

I know this is a basic question, but I cannot seem to focus today. Thanks for your time.

jonysuise Ten Centuries Points: 1206 More actions · Answer 3

DaveDB (10/17/2013)
I know this is a basic question, but I cannot seem to focus today. Thanks for your time.

Anytime 🙂

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

arnipetursson SSCertifiable Points: 6557 More actions · Answer 4

Surely you meant:

SELECT DATEADD(d, -2, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))

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

returns: 2013-10-31 23:59:59.000

jonysuise Ten Centuries Points: 1206 More actions · Answer 5

arnipetursson (10/17/2013)
Surely you meant:
SELECT DATEADD(d, -2, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))
SELECT DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))
returns: 2013-10-31 23:59:59.000

Indeed. Thanks 🙂

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

We can cheat this just a little more if you want...

SELECT DATEADD(dd,-2,DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chaitutheprince SSC Enthusiast Points: 113 More actions · Answer 7

Select dateadd(dd,-(day(getdate()))-1,getdate())

or

Select Dateadd(d,-DATEPART(d,DateAdd(m,1,dateadd(dd,-1,getdate())))-1,DateAdd(m,1,dateadd(dd,-1,getdate())))

any query will get the lastdayofmonth-1

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

Let's say for the sake of argument and amusement you had a really, really fast FUNCTION for generating a calendar table. Like the following:

CREATE FUNCTION [dbo].[GenerateCalendar]

(

@FromDate DATETIME,

@NoDays INT

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)

--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)

WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows

E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows

E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows

cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

SELECT [SeqNo] = t.N,

[Date] = dt.DT,

[Year] = dp.YY,

[YrNN] = dp.YY % 100,

[YYYYMM] = dp.YY * 100 + dp.MM,

[BuddhaYr] = dp.YY + 543,

[Month] = dp.MM,

[Day] = dp.DD,

[WkDNo] = DATEPART(dw,dt.DT),

[WkDName] = CONVERT(NCHAR(9),dp.DW),

[WkDName2] = CONVERT(NCHAR(2),dp.DW),

[WkDName3] = CONVERT(NCHAR(3),dp.DW),

[JulDay] = dp.DY,

[JulWk] = dp.DY/7+1,

[WkNo] = dp.DD/7+1,

[Qtr] = DATEPART(qq,dt.Dt),

[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,

[LdOfMo] = DATEPART(dd,dp.LDtOfMo),

[LDtOfMo] = dp.LDtOfMo

FROM cteTally t

CROSS APPLY ( --=== Create the date

SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)

) dt

CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"

-- (Cascading CROSS APPLY, Acourtesy of ChrisM)

SELECT YY = DATEPART(yy,dt.DT),

MM = DATEPART(mm,dt.DT),

DD = DATEPART(dd,dt.DT),

DW = DATENAME(dw,dt.DT),

Dy = DATEPART(dy,dt.DT),

LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

) dp

Then the code you need is as simple as:

SELECT [Last Day of Month -1]=LDtOfMo-1

FROM dbo.GenerateCalendar(GETDATE(),1);

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St