last 12 month data - 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)

last 12 month data

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-09-19 : 10:16:31

Hi all,

need help to figure out that how can i get last 12 month of data for any given date. i have start and end date.

should i do like datediff(mm, 12, getdate())
??
thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-19 : 10:27:45

there can be some variations depending on if you want 12 months from "now" down to the millisecond, or from earliest time today or from beginning of the month, etc, but one simple way is:

where datediff(day, <myDateCol>, getdate()) < 365

EDIT: (this one is probably better)
or
where <myDateCol> >= dateadd(year, -1, getdate())
endEdit

(i'm not sure your your startdate and enddate relates to 12 months from a given date)

Be One with the Optimizer
TG

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-09-19 : 10:35:31

i have category like i want the data between today(9/19/2006) and 9/18/2005(last year) and want to repeate everyday!!!

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 10:40:29

Then you most probably want to use a date generator as Michael Valentine Jones's. He has a function that get you all days, not just the dates in our tables with sales.

Peter Larsson
Helsingborg, Sweden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-19 : 10:46:27

>>and want to repeate everyday!!!
not sure what you mean by this. Do you want to run the query "everyday"? or as Peso suggests, Have everyday represented in your results whethere you have data for it or not.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-19 : 10:48:18

I'm not sure exactly sure what you want, but this is how to query for all dates in a date range. This will return data for dates 2005-08-18 through 2009-09-19.


select
	*
from
	MyTable
where
	MyTableDate >= '20050818'  and
	MyTableDate <  '20090920'

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources