Last three months

Question

Post reply

Last three months

gordon.davis

Old Hand

Points: 300
More actions
August 1, 2012 at 11:46 am

#278773

Hi Could you Please Help me to write a function by default it should written last three months startdate and enddate from current month for me its failing for 31st.Please help
function should returns
Sartdate Enddate
05/01/2012 05/31/2012
06/01/2012 06/30/2012
07/01/2012 07/31/2012

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

THis?

IF OBJECT_ID (N'dbo.LastThreeMonths', N'IF') IS NOT NULL

DROP FUNCTION dbo.LastThreeMonths;

GO

CREATE FUNCTION dbo.LastThreeMonths()

RETURNS TABLE

AS

RETURN

WITH CurrentDate AS

(

SELECT ThisMonthFirstDay = DATEADD( MM, DATEDIFF(MM,0,GETDATE()) ,0)

)

, Numbers(N) AS

(

SELECT -1

UNION ALL SELECT -2

UNION ALL SELECT -3

)

SELECT StartDate =CrsApp.StartDates

,EndDate = DATEADD(DD,-1, DATEADD(MM,1,CrsApp.StartDates))

FROM CurrentDate CD

CROSS APPLY (SELECT DATEADD(MM,N.N,CD.ThisMonthFirstDay)

FROM Numbers N ) CrsApp(StartDates)

GO

Usage:

SELECT *

FROM dbo.LastThreeMonths()

{Edit : Added Usage}

Charles Hearn SSCrazy Points: 2680 More actions · Answer 2

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

CapnHector SSCoach Points: 16143 More actions · Answer 3

Here is the basic format to get the first and last day of the previous 3 months.

SELECT DATEADD (MM, DATEDIFF (mm, 0, GETDATE()) - 1 - N, 0), DATEADD (MM, DATEDIFF (mm, 0, GETDATE()) - N, 0) - 1

FROM (VALUES (0),(1),(2)) A(N))

Its based off of the first of this month:

SELECT DATEADD (MM, DATEDIFF (MM, 0, GETDATE()), 0)

Then because adding and subtracting integers to a datetime we can subtract 1 and get the last day of last month:

SELECT DATEADD (MM, DATEDIFF (MM, 0, GETDATE()), 0) - 1

to get the first day of last month we just add one less month to the 0 date (1900-01-01):

SELECT DATEADD (MM, DATEDIFF (MM, 0, GETDATE()) - 1, 0)

and then since we have last month we subtract N using the values (0),(1),(2) to get the last 3 months.

Depending on what you need to use this for would determine how i would write the function.

EDIT: looks like i was beaten to it.

For the function it would look like this:

CREATE FUNCTION Last3Months ()

RETURNS TABLE

AS

RETURN

SELECT DATEADD (MM, DATEDIFF (mm, 0, GETDATE()) - 1 - N, 0) AS StartOfMonth, DATEADD (MM, DATEDIFF (mm, 0, GETDATE()) - N, 0) - 1 AS EndOfMonth

FROM (VALUES (0),(1),(2)) A(N)

GO

And usage:

SELECT * FROM Last3Months()

ORDER BY StartOfMonth

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]

gordon.davis Old Hand Points: 300 More actions · Answer 4

Hi,

Could you Please help me to get Output like below.

SNLabel StartDate EndDate

1 May 122012-05-01 2012-05-31

2 Jun 122012-06-01 2012-06-30

3 Jul 122012-07-01 2012-07-31

and also if the current month is 02-01-2012 then

SN Label StartDate EndDate

1 Jan 12 2012-01-01 2012-01-31

2 DEC 11 2012-12-01 2012-12-31

3 NOV 11 2012-11-01 2012-11-30

Appreciate your help...

CapnHector SSCoach Points: 16143 More actions · Answer 5

gordon.davis (8/1/2012)
Hi,
Could you Please help me to get Output like below.
SNLabel StartDate EndDate
1 May 122012-05-01 2012-05-31
2 Jun 122012-06-01 2012-06-30
3 Jul 122012-07-01 2012-07-31
and also if the current month is 02-01-2012 then
SN Label StartDate EndDate
1 Jan 12 2012-01-01 2012-01-31
2 DEC 11 2012-12-01 2012-12-31
3 NOV 11 2012-11-01 2012-11-30
Appreciate your help...

in either my or cold coffee's functions you would replace GETDATE() with the paramater @Date you pass into the function. For the way to convert the functions please see the following link for the CREATE FUNCTION article on BOL

http://msdn.microsoft.com/en-us/library/ms186755.aspx

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]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

I have edited the function to add the extra columns u asked for and laso add an Input Parameter. If you wnat to get last 3 months from the current date, pass NULL as the i/p param. If you 3 months from a specific date, pass the date.

IF OBJECT_ID (N'dbo.LastThreeMonths', N'IF') IS NOT NULL

DROP FUNCTION dbo.LastThreeMonths;

GO

CREATE FUNCTION dbo.LastThreeMonths(@Input DATETIME)

RETURNS TABLE

AS

RETURN

WITH CurrentDate AS

(

SELECT ThisMonthFirstDay = DATEADD( MM, DATEDIFF(MM,0,ISNULL(@Input,GETDATE())) ,0)

)

, Numbers(N) AS

(

SELECT -1

UNION ALL SELECT -2

UNION ALL SELECT -3

)

SELECT SlNo = CrsApp.N + 4

,Label = LEFT( DATENAME(M, CrsApp.StartDates) ,3) + ' ' + RIGHT(YEAR(CrsApp.StartDates),2)

,StartDate =CrsApp.StartDates

,EndDate = DATEADD(DD,-1, DATEADD(MM,1,CrsApp.StartDates))

FROM CurrentDate CD

CROSS APPLY (

SELECT N , DATEADD(MM,N.N,CD.ThisMonthFirstDay)

FROM Numbers N

) CrsApp(N, StartDates)

GO

Usage:

-- Current Date

SELECT *

FROM dbo.LastThreeMonths(NULL)

-- Specific Date

SELECT *

FROM dbo.LastThreeMonths('2012-02-01')