Author |
Topic |
Chamark
Starting Member
28 Posts |
Posted - 2010-08-18 : 10:10:48
|
Using MS-SQL2008- Beginner - I need a couple of date routines and am having difficulties. On the first date needed I supply a date and I want to get the data for a column 12 months previous to that date. Attempting to get data for actual_month for 06/01/2009Select Actual_monthFrom DBWhere Yearmonth = DATEADD(yy, -1, Yearmonth) and yearmonth = '06/01/2010' I get no results - I know data is available 'cause I get it when I doSelect Actual_monthFrom DBWhere Yearmonth = '06/01/2009'I also need to be able to get data for last month of current year based on again on date provided - example '06/01/2010' - I need to get data for '12/01/2010'Any help is greatly appreciated. |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-08-18 : 10:26:02
|
Where Yearmonth >= DATEADD(yy, -1, Yearmonth) and yearmonth <= '06/01/2010' KH[spoiler]Time is always against us[/spoiler] |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-08-18 : 10:30:03
|
[code]-- On the first date needed I supply a date --and I want to get the data for a column 12 months previous to that date. DECLARE @Isupplydate datetime; SET @Isupplydate = '2001-09-11'SELECT @Isupplydate, DATEADD(yy,-1,@Isupplydate)SELECT * FRMO myTable99WHERE DateCol BETWEEN DATEADD(yy,-1,@Isupplydate) AND @Isupplydate[/code]And what was the second one?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
MSquared
Yak Posting Veteran
52 Posts |
Posted - 2010-08-18 : 10:30:25
|
between works tooSelect Actual_monthFrom DBWhere Yearmonth between DATEADD(yy, -1, Yearmonth) and yearmonth = '06/01/2010' |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-08-18 : 10:34:02
|
-- I also need to be able to get data for last month of current year -- based on again on date provided -- example '06/01/2010' - I need to get data for '12/01/2010'Well that contradictory..I'm guessing you just mean-- I also need to be able to get data for last month of the year-- based on again on date provided DECLARE @Isupplydate datetime; SET @Isupplydate = '2001-09-11'SELECT CONVERT(datetime,'12/1/'+CONVERT(char(4),YEAR(@Isupplydate))) Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
Chamark
Starting Member
28 Posts |
Posted - 2010-08-18 : 10:34:36
|
Thanks khtan - that worked for the previous year - now I need to figure out how to get last month of current year. Thanks again |
|
|
X002548
Not Just a Number
15586 Posts |
|
Chamark
Starting Member
28 Posts |
Posted - 2010-08-18 : 10:37:10
|
Thanks to you all - this works - I appreciate your help very much |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-08-18 : 22:34:34
|
quote: Originally posted by Chamark Thanks khtan - that worked for the previous year - now I need to figure out how to get last month of current year. Thanks again
you want the 1st of Dec of 31st of Dec of current year ? KH[spoiler]Time is always against us[/spoiler] |
|
|
|