Having problems mixing variables into a 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

General SQL Server Forums

New to SQL Server Programming

Having problems mixing variables into a date

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-09 : 16:03:38

I am writing a stored procedure that uses inputs of @month and @year to pull the last 12 months of data from a database. Before I added @month and @year it worked fine, but I can't figure out how to make it be intelligent and know that december comes before january. My latest attempt is below. I thought I could take the two variables and make them work insisde a datetime datatype, but its failing. I don't know if I'm using too many apostrphes or not enough.

Any help would be appreciated! Here is my attempt.



DECLARE @month int
set @month = 5
DECLARE @year int
set @year = 2010

	SELECT 
		description,
		Date = convert(datetime,description),
		projectid, 
		GCO_001,
		'Voluntary Attrition' = HCR_046,
		'Involuntary Attrition' = HCR_047, 
		'Total Attrition' = HCR_048,
		'Percentage' = HCR_048/(HCR_024 + HCR_025), 
		HCR_024 + HCR_025 
	FROM
		vw_human_capital
	WHERE
		convert(datetime,description) <= convert(datetime,'@year'-'@month'-01 00:00:00.000)
		--month(convert(datetime,description)) <= 5 /* @month */ and year(convert(datetime,description)) <= 2010 --@year
		and description>=dateadd(month,datediff(month,0,getdate())-12,0) 
		and	description<dateadd(month,datediff(month,0,getdate())+1,0) 
	ORDER BY
		convert(datetime,description) desc

Craig Greenwood

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-09 : 16:13:22

SELECT DATEADD(MONTH, 12 * @Year - 22811 + @Month, 0),
DATEADD(MONTH, 12 * @Year - 22800 + @Month, 0)

N 56°04'39.26"
E 12°55'05.63"

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-09 : 16:17:13

Peso,
That won't do it. My problem is in the where statement. Specifically this line:

convert(datetime,description) <= convert(datetime,'@year'-'@month'-01 00:00:00.000)

How can I make this function? Sorry I should have explained that better in my question. The line below that WORKS, but its not intelligent. If I use that February 2009 will appear if I enter March 2010 because February the month comes before March. I hope that makes sense.

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-09 : 17:27:04

There are a bunch of ways to convert values to a datetime. I *think* Peso is suggesting a way to convert the Year and Month parameters into a date range. Here is another way to do a conversion:

DECLARE @month int
set @month = 5
DECLARE @year int
set @year = 2010

SELECT DATEADD(MONTH, @month - 1, DATEADD(YEAR, @Year - 1900, 0))
--OR
SELECT CAST(CAST(@YEAR AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(@Month AS VARCHAR(2)), 2) + '-01' AS DATETIME)

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-09 : 17:56:11

Hmm...

Maybe I don't understand what you guys are suggesting. Here is what I think. I think I can do it in the select statement. I am aware of basic date manipulation. My problem is using this in the WHERE statement. You guys both suggested select statement operations, which I don't argue with at all. But again, I may be misreading your intentions. How can I make my query use my date variables to always look at the last 12 dynamic months? I guess that's the root of my question.

I will study your suggestions above in more detail. I hope I am speaking my intentions correctly and they are being understood.

Thanks again,

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-09 : 18:32:05

Maybe you can describe the date logic a bit more. If you want the last 12 months of data how does the Year and the Month that are passed in affect that?

Assuming your logic is corrcet, it looks like you are trying to limit the data to the last 12 months. Then further restrict that set to only data less than or equal to that Year & Month passed in. Is that what you are actually want?

Or are you trying to start at the Year & Month passed in and go back 12 months from that point? Or other?

craigwg
Posting Yak Master

154 Posts

Posted - 2010-06-10 : 00:14:17

Lamprey,

I am indeed trying to limit the result to return the previous 12 months from the date of request (only the month and year are input, so we don't have to divy up days). In actuality, most requests will simply use getdate() as the input. But sometimes they will input February 2010 (or any month) as their starting date, in which case I need to return Feb 2009 through Februaary 2010.

I hope that helps clarify.

Craig Greenwood

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-10 : 03:00:55

Works for me!

DECLARE	@Year SMALLINT,
	@Month  TINYINT

SELECT	@Year = 2010,
	@Month = 5

SELECT	DATEADD(MONTH, 12 * @Year - 22811 + @Month, 0) AS FromDate,
	DATEADD(MONTH, 12 * @Year - 22800 + @Month, 0) AS ToDate

/*
SELECT	*
FROM	YourTable
WHERE	YourDateTimeColumn >= DATEADD(MONTH, 12 * @Year - 22811 + @Month, 0)
	AND YourDateTimeColumn < DATEADD(MONTH, 12 * @Year - 22800 + @Month, 0)
*/

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-10 : 11:13:56

Peso,

Assuming I understand the logic correctly, your FromDate is off by one month. I think it just needs a -1:
DATEADD(MONTH, 12 * @Year - 22811 + @Month - 1, 0)

I'm not sure I'm awake yet, so I might be off on this though.. :)

Similar to Peso's code, jsut done a different way:

DECLARE	@Year SMALLINT,
	@Month  TINYINT

SELECT	@Year = 2010,
	@Month = 5

SELECT DATEADD(YEAR, - 1, DATEADD(MONTH, @month - 1, DATEADD(YEAR, @Year - 1900, 0))) AS FromDate,
	DATEADD(MONTH, @month, DATEADD(YEAR, @Year - 1900, 0)) AS ToDate

SELECT	*
FROM	YourTable
WHERE	YourDateTimeColumn >= DATEADD(YEAR, - 1, DATEADD(MONTH, @month - 1, DATEADD(YEAR, @Year - 1900, 0)))
	AND YourDateTimeColumn < DATEADD(MONTH, @month, DATEADD(YEAR, @Year - 1900, 0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources