SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DateDiff DateAdd Date Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Chamark
Starting Member

USA
28 Posts

Posted - 08/18/2010 :  10:10:48  Show Profile  Reply with Quote
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/2009
Select Actual_month
From DB
Where 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 do
Select Actual_month
From DB
Where 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)

Singapore
17681 Posts

Posted - 08/18/2010 :  10:26:02  Show Profile  Reply with Quote
Where Yearmonth >= DATEADD(yy, -1, Yearmonth) and yearmonth <= '06/01/2010'


KH
Time is always against us

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/18/2010 :  10:30:03  Show Profile  Reply with Quote

-- 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 myTable99
WHERE DateCol BETWEEN  DATEADD(yy,-1,@Isupplydate) AND @Isupplydate




And what was the second one?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

MSquared
Yak Posting Veteran

52 Posts

Posted - 08/18/2010 :  10:30:25  Show Profile  Reply with Quote
between works too

Select Actual_month
From DB
Where Yearmonth between DATEADD(yy, -1, Yearmonth) and yearmonth = '06/01/2010'
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/18/2010 :  10:34:02  Show Profile  Reply with Quote
-- 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)))



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Chamark
Starting Member

USA
28 Posts

Posted - 08/18/2010 :  10:34:36  Show Profile  Reply with Quote
Thanks khtan - that worked for the previous year - now I need to figure out how to get last month of current year. Thanks again
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/18/2010 :  10:34:49  Show Profile  Reply with Quote
quote:
Originally posted by MSquared

between works too



Brilliant!!!!!





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Chamark
Starting Member

USA
28 Posts

Posted - 08/18/2010 :  10:37:10  Show Profile  Reply with Quote
Thanks to you all - this works - I appreciate your help very much
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 08/18/2010 :  22:34:34  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000