where condition - 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)

where condition

Author

Topic

wawansur
Starting Member

44 Posts

Posted - 2009-08-10 : 22:44:39

I want to get data 1 month earlier, with parameter this month and this year. I had problem when I have parameter month=1(january).
how to solve this?

Rgds

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-10 : 23:33:57

[code]
declare @year int,
@month int

select @year = 2009,
@month = 8

select begin_of_prev_mth = dateadd(month, datediff(month, 0, getdate()) - 1, 0),
end_of_prev_mth = dateadd(month, datediff(month, 0, getdate()), -1),
begin_of_curr_mth = dateadd(month, datediff(month, 0, getdate()), 0),
end_of_curr_mth = dateadd(month, datediff(month, 0, getdate()) + 1, -1)
[/code]

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

wawansur
Starting Member

44 Posts

Posted - 2009-08-11 : 00:21:22

declare @year int,
@month int

select @year = 2009,
@month = 8

select begin_of_prev_mth = dateadd(month, datediff(month, 0, getdate()) - 1, 0),
end_of_prev_mth = dateadd(month, datediff(month, 0, getdate()), -1),
begin_of_curr_mth = dateadd(month, datediff(month, 0, getdate()), 0),
end_of_curr_mth = dateadd(month, datediff(month, 0, getdate()) + 1, -1)

when the @year and @month used in query?

Rgds

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-11 : 01:14:20

sorry . . . C & P problem. Missed out the other section of the query


DECLARE @YEAR	int,
	@MONTH	int

SELECT	@YEAR 	= 2009,
	@MONTH	= 8

SELECT	begin_of_prev_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0),
	end_of_prev_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1),
	begin_of_curr_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
	end_of_curr_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)

SELECT	begin_of_month		= DATEADD(MONTH, @MONTH - 1, DATEADD(YEAR, @YEAR - 1900, 0)),
	end_of_month		= DATEADD(MONTH, @MONTH, DATEADD(YEAR, @YEAR - 1900, -1)),
	begin_of_prev_month	= DATEADD(MONTH, @MONTH - 2, DATEADD(YEAR, @YEAR - 1900, 0)),
	end_of_prev_month	= DATEADD(MONTH, @MONTH - 1, DATEADD(YEAR, @YEAR - 1900, -1))

the 1st part is how to find start and end of current / prev month, the 2nd part is based on input @year / @month

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

wawansur
Starting Member

44 Posts

Posted - 2009-08-11 : 04:11:46

quote:

Originally posted by khtan

sorry . . . C & P problem. Missed out the other section of the query


DECLARE @YEAR	int,
	@MONTH	int

SELECT	@YEAR 	= 2009,
	@MONTH	= 8

SELECT	begin_of_prev_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0),
	end_of_prev_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1),
	begin_of_curr_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
	end_of_curr_mth 	= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)

SELECT	begin_of_month		= DATEADD(MONTH, @MONTH - 1, DATEADD(YEAR, @YEAR - 1900, 0)),
	end_of_month		= DATEADD(MONTH, @MONTH, DATEADD(YEAR, @YEAR - 1900, -1)),
	begin_of_prev_month	= DATEADD(MONTH, @MONTH - 2, DATEADD(YEAR, @YEAR - 1900, 0)),
	end_of_prev_month	= DATEADD(MONTH, @MONTH - 1, DATEADD(YEAR, @YEAR - 1900, -1))

the 1st part is how to find start and end of current / prev month, the 2nd part is based on input @year / @month

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

Thax

Rgds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources