year/month in integer field - 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 2005 Forums

Transact-SQL (2005)

year/month in integer field

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2009-10-07 : 14:31:29

I have an integer field that is a year/month. The data looks like "200901" I need to be able to select all the values in there that are 25 momths back. So there could be 200701 , 200704, 200712, 200802

Dave
Helixpoint Web Development
http://www.helixpoint.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-07 : 14:51:50

Yoicks! Try this

declare @ym int
declare @today datetime

set @today = dateadd(month,datediff(month,0,getdate()),0)

select @today

set @ym = 200901

select *
from
yourTable
where
convert(datetime,convert(char(10), @ym*100+1)) >= dateadd(month,-25,@today)

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-07 : 15:11:33

Here is an alternate method that can use an index on your Year/Month column, should one exist:

DECLARE @YearMonth INT

SET @YearMonth = 
	(YEAR(DATEADD(MONTH, -25, CURRENT_TIMESTAMP)) * 100) 
	+ MONTH(DATEADD(MONTH, -25, CURRENT_TIMESTAMP))

SELECT *
FROM MyTable
WHERE MyColumn >= @YearMonth

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-07 : 15:20:18

SELECT * FROM Table1 WHERE Col1 >= CAST(CONVERT(CHAR(6), DATEADD(MONTH, -25, GETDATE()), 112) AS INT)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources