Previous 6 months from Parameter Date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Previous 6 months from Parameter Date

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-09-13 : 09:44:57

Is there a way to get the previous 6 months from the parameter date. I would like to edit the code below to go back 6 months from the selected start date.

SurveyDate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0)) AND (SurveyDate < DATEADD(mm, DATEDIFF(dd, 0, GETDATE()), 0))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-13 : 09:56:18

[code]dateadd(month, -6, getdate())[/code]

or

[code]dateadd(month, datediff(month, 0, getdate()) - 6, 0)[/code]

if you want date of 1st of the month

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:19:49

see

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-09-13 : 10:34:03

I know how to get previous month, day, week, and quarter. I have a chart in my report that gives a six month trend from the previous month. The complication that I currently have is, I need it to be a little bit more flexible. I need the chart data to go back six months from the parameter date versus six months back from the current date. I currently have the code below in my where clause.

SurveyDate >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0)) AND (SurveyDate < DATEADD(mm, DATEDIFF(dd, 0, GETDATE()), 0))

EX: Start Date = 03/3/2012 End Date 03/31/2012

My chart data goes back six months from June versus going back six months from March

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:37:35

sorry you explanation is a bit confusing

you say
I need the chart data to go back six months from the parameter date versus six months back from the current date

and then
My chart data goes back six months from June versus going back six months from March

so was the passed parameter value in June ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-09-13 : 10:53:02

The date range I selected was March 1, 2012 to March 31, 2012. My chart is build to give me the last six months trend (line chart). The first data point in the chart goes back six months from the previous month (ex: If I ran the report today, the chart is going back six month from the previous month [august]. I need the chart to go six months back from March.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 11:21:56

then it should be

SurveyDate >= DATEADD(mm, DATEDIFF(mm, 0,@Yourpasseddatevalue) - 6, 0)) AND (SurveyDate < DATEADD(mm, DATEDIFF(mm, 0, @Yourdatevalue), 0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources