dateadd - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

dateadd

Author

Topic

cathiec
Starting Member

3 Posts

Posted - 2006-02-24 : 09:32:53

select year(DATEADD(m,-1,(month(@intMonth)+year(@intYear))))

select year(DATEADD(m,-1,(month(1)+year(2006))))

i am using the above dateadd funtion. i use two variables in my query intMonth and intYear.

I want to use the dateadd function to return the year based on the month and year passed in as parameters.

basically if the year is 2006 and the month is jan (01) i want the year to be 2005 as the function should take one month away from the month and yeat thus giving 12/2005. i just need to be able to say that if the month is jan and the year is 2006 then the year returned from this query should be 2005. before jan 2006 is dec (2005). i have to use the two variables intMonth and intYear but i cant figure out how to pass these to the dateadd function and get the right year back,

the select statement above returns 1905 - taking one from the month and 100 from the year

would really appreciate any help with this one!
thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-24 : 09:40:00

Is this what you want ?

declare
	@intYear	int,
	@intMonth	int

select	@intYear  = 2006,
	@intMonth = 1

select	year(dateadd(month, -1, dateadd(year, @intYear - 1900, dateadd(month, @intMonth - 1, 0))))

----------------------------------
'KH'
It is inevitable

cathiec
Starting Member

3 Posts

Posted - 2006-02-24 : 09:56:58

yes this is perfect - but if i use it in my query it doesnt work if i change the month parameter to february - the query is based on a self join on a table when i get figures for one month and compare them to the previos month - hence the problem with trying to get dec 05 from the date jan 06

select isnull(b.sumofriskcat,0) as sumFristMonth, isnull(a.sumofriskcat,0) as sumSecondMonth , isnull(a.sumofriskcat,0) -isnull(b.sumofriskcat,0) as monthlyChange, a.strName, a.strPlantareaname
from tblSafeTrackIT_Data_HazardMonthlyChanges a , tblSafeTrackIT_Data_HazardMonthlyChanges b

where b.intmonth = Month(DATEADD(m,-1,month(@intMonth))) and b.intyear=year(dateadd(month, -1, dateadd(year, @intYear - 1900, dateadd(month, @intMonth - 1, 0))))and a.intmonth = @intMonth and a.intyear= @intYear
and b.intAreaID =* a.intareaid
and b.intriskid =* a.intriskid

--b is the first month - the month to compare current figures to
--a is the second month
order by a.strPlantAreaName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-25 : 00:24:40

are you trying to get the previous month from this Month(DATEADD(m,-1,month(@intMonth))) ?
Try this :

declare
	@intYear	int,
	@intMonth	int

select	@intYear  = 2006,
	@intMonth = 1

select	year(dateadd(month, -1, dateadd(year, @intYear - 1900, dateadd(month, @intMonth - 1, 0))))  as prev_mth_yy,
	month(dateadd(month, -1, dateadd(year, @intYear - 1900, dateadd(month, @intMonth - 1, 0)))) as prev_mth_mm

----------------------------------
'KH'
It is inevitable

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-02-28 : 06:14:10

FWIW, here's another variation


SELECT YEAR(DATEADD(DAY, -1, LTRIM(@intYear * 10000 + @intMonth * 100 + 1))) as prev_mth_yy
 , MONTH(DATEADD(DAY, -1, LTRIM(@intYear * 10000 + @intMonth * 100 + 1))) as prev_mth_mm

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

cathiec
Starting Member

3 Posts

Posted - 2006-02-28 : 06:25:01

thanks guys!
here's how i ended up doing it. a bit crude but i was able to create the variable @youractualdate in the vb code and parsed it in the query

@intMonth int,
@intYear int,
@youractualdate varchar(50)

AS

select isnull(b.sumofriskcat,0) as sumFristMonth, isnull(a.sumofriskcat,0) as sumSecondMonth , isnull(a.sumofriskcat,0) -isnull(b.sumofriskcat,0) as monthlyChange, a.strName, a.strPlantareaname
from tblSafeTrackIT_Data_HazardMonthlyChanges a , tblSafeTrackIT_Data_HazardMonthlyChanges b

where b.intmonth = Month(DATEADD(m,-1,cast(@youractualdate as smalldatetime))) and b.intyear= year(DATEADD(m,-1,cast(@youractualdate as smalldatetime))) and a.intmonth = @intMonth and a.intyear= @intYear
and b.intAreaID =* a.intareaid
and b.intriskid =* a.intriskid

--b is the first month - the month to compare current figures to
--a is the second month
order by a.strPlantAreaName,a.strName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources