Finding set dates given a single date

Question

Finding set dates given a single date

LoosinMaMind

Hall of Fame

Points: 3366
More actions
November 14, 2012 at 11:43 am

#266959

Hi
I must have done something really bad in a previous life as most of my current work involves dates and calculations from said dates. (only joking)
Question / Problem is.......
From todays getdate (), I need to establish the following .....
a) the last date of this current month : ie '2012-11-30 00:00:00.000'
b) the last date of the next month : ie '2012-12-31 00:00:00.000'
and
c) the last date of the subsequent month : ie '2013-01-31 00:00:00.000'
So basically from any given date (it will always be getdate () ) i need the get the last days of the current month and the last calender days of the next two months in that date format.
Any help would be greatly received
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

from my notes, which i swear i use daily when fiddling with dates:

--another midnight
CAST(DATEDIFF(dd,0,somedatetime) AS DATETIME).
this is one of the coolest things to use once you get your head wrapped around it.
if you select DATEDIFF(wk,0,getdate())
this will return week '5763' , which is the # of weeks (wk) from the beginning of SQL time 01/01/1900
by adding it to zero(SQL starting date '01/01/1900') with DATEADD(which returns a datetime), you get the first day of the beginning of that week 5763, which is Monday. Mentally, i sometimes think of this method as "truncating" all the time portion, and then selecting the begining of the period.
the same concept works when you add months, years, or quarters...and even hours minutes seconds.....you get the # of [unit you selected] since the beginning of SQL Time, and by adding that #periods to zero(SQL start date), you get the beginning of that period.
run this query so you have everything in front of you:
select
getdate(), --2010-06-16 10:08:47.680
DATEDIFF(wk,0,getdate()), --week '5763' from the beginning of SQL time 01/01/1900
DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --by adding zero, we return a date that is the first day of the week 5763
here's a collection of "firsts and lasts" i've saved in my snippets:
--find the first business day (Monday) of this month
select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)
--find the last day of the prior month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
--find the third friday of this month:
--14 two weeks plus the M-F offset of 4
select DATEADD(dd,18,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0))
select DATEADD(wk,2,DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)) )
--last business day(Friday) of the prior month...
datename(dw,dateadd(dd,-3,DATEADD(wk,
DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0)))
--Monday of the Current Week
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--Friday of the Current Week
select dateadd(dd,4,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0))
--First Day of this Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--First Day of the Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--First Day of the Quarter
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--Last Day of Prior Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))
--Last Day of Current Month
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))
--Last Day of Current Year
select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CapnHector SSCoach Points: 16143 More actions · Answer 2

First thing we need to do is find the first of next month with the following:

DATEADD(MM,DATEDIFF(MM,0,GETDATE()) + 1,0)

Next we can just subtract a day to get the last day of this month. From there we can just replicate out for as many months as we need.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 3

Thank you both so much.

I say it after every sucessfull post, and I'll say it again...."I love the people on this site "

Cheers

LoosinMaMind Hall of Fame Points: 3366 More actions · Answer 4

Thanks Lowell, your snippets will be bery helpful in the future.

Cheers

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

LoosinMaMind (11/14/2012)
Thanks Lowell, your snippets will be bery helpful in the future.
Cheers

Just checking... you understand WHY the code snippets work?

--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)
Intro to Tally Tables and Functions

Dwain Camps SSC Guru Points: 86913 More actions · Answer 6

I have a GenerateCalendar FUNCTION (rewritten by Jeff Moden) that can also be used for this. Thought I'd share it below.

The query using it is pretty simple but the other methods shown here will most likely be faster.

SELECT DISTINCT TOP 3 LDtOfMo

FROM GenerateCalendar(GETDATE(), 93)

Here's this general utility iTVF:

CREATE FUNCTION GenerateCalendar

(

@FromDate DATETIME,

@NoDays INT

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== 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

Thanks again to Jeff for showing me the path.

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