Calendar logic, select last day of the month

Question

Calendar logic, select last day of the month

emily-1119612

SSCarpal Tunnel

Points: 4166
More actions
August 6, 2009 at 9:29 am

#208358

Thanks to anyone who can help. I need to select only the last day of the month. Emily
Expected results
1/1/2009
2/28/2009
etc
CREATE TABLE dbo.MyCalendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED
)
GO
SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
SET @dt = '20090101'
WHILE @dt < '20090731'
BEGIN
INSERT dbo.MyCalendar(dt) SELECT @dt
SET @dt = @dt + 1
END
SELECT * FROM MyCalendar

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

A little confused as to what you are asking. Are you wanting the to return the last day of a month based on the current or given date, such as:

2009-07-12 --> 2009-07-31

If so, then what you want is this:

declare @ThisDate datetime;

set @ThisDate = getdate();

select @ThisDate, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1)

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 2

Lynn Pettis (8/6/2009)

A little confused as to what you are asking. Are you wanting the to return the last day of a month based on the current or given date, such as:

No, I want the last day of every month to be returned from my select statement. So for my sample data I would want to return

20090131

20090228

20090331

20090430

20090531

20090630

20090730

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

emily (8/6/2009)

Lynn Pettis (8/6/2009)
A little confused as to what you are asking. Are you wanting the to return the last day of a month based on the current or given date, such as:
No, I want the last day of every month to be returned from my select statement. So for my sample data I would want to return
20090131
20090228
20090331
20090430
20090531
20090630
20090730
Thanks

Still not fully understanding what you are trying to accomplish. Other than the fact that July ends on 2009-07-31 not 2009-07-30, what is your criteria for determining the date range for the End of Months? Are you looking for the end of the current month plus the following six months? I'm asking, as I try very hard not to hard code dates or other criteria in my code so that I don't have to worry about modifying it as time goes by.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

any good?

SELECT dt, DATEADD(dd, - DAY(DATEADD(m, 1, dt)), DATEADD(m, 1, dt)) AS EOM

FROM dbo.MyCalendar

regards Graham

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 5

[/quote]

>>Still not fully understanding what you are trying to accomplish.

Ok let me try again and thanks for working with me.

>>Other than the fact that July ends on 2009-07-31 not 2009-07-30,

Correct

My six months is just sample data my actual data is several years.

>>what is your criteria for determining the date range for the End of Months?

I am not trying to generate new data rather, just select from an existing set of data. There is no criteria for the "date range for the End of Months"

Given my existing data I want to select the MAX date of every month, for which at least one record exists for that month. Since my data includes every day of the month, MAX would be the last day of the month.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Okay, now we are actually getting somewhere. How about providing us with the actual problem you are trying to resolve? Rather than asking piecemeal questions, put the whole pie on the table so we can help.

You may want to provide the DDL for the table, some sample data, and expected results so that we can provide you with tested code.

GSquared SSC Guru Points: 260824 More actions · Answer 7

If you have a Numbers table:

select dateadd(month, number-1, '1/1/2000'), dateadd(month, number, '1/1/2000')-1

from dbo.Numbers;

That will give you the first and last date of every month. You can join data to that, and so on.

If you don't have a Numbers table, it's just a table with integers in it. This version goes from 1 to 10,000:

create table dbo.Numbers (Number int primary key);

go

insert into dbo.Numbers(Number)

select top 10000 row_number() over (order by t1.object_id)

from sys.all_objects t1

cross join sys.all_objects t2;

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 8

Lynn Pettis (8/6/2009)
Okay, now we are actually getting somewhere. How about providing us with the actual problem you are trying to resolve? Rather than asking piecemeal questions, put the whole pie on the table so we can help.
You may want to provide the DDL for the table, some sample data, and expected results so that we can provide you with tested code.

I though I did. 🙂 See the create for the table, the script that inserts data and my desired results above.

This should work.

SELECT *

/*

Ok so there are more fields here, so I didn't really provide the actual problem I was trying to solve.

My employer will terminate me if my DDL and sample data is even remotely similar to the real thing.

So yes I para-phrased my problem, something may have been lost in translation

*/

FROM dbo.MyCalendar

WHERE dt IN

(

SELECT

DISTINCT

DATEADD(dd, - DAY(DATEADD(m, 1, dt)), DATEADD(m, 1, dt)) AS EOM

FROM dbo.MyCalendar

)

Thanks all!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Do you have access to teh AdventureWorks sample database? Can you use one or more of the tables there to formulate a similar problem?