1 YEAR LESS THEN @DATE - 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

SQL Server Development (2000)

1 YEAR LESS THEN @DATE

Author

Topic

cardgunner

326 Posts

Posted - 2008-06-26 : 08:45:01

I have a working statement, however I have a gut feeling that it may run into problems, maybe not.

I need to call up a year ago last month. So it being June 2008 now, I need to call up May 2007.

declare @s_date 		datetime
declare @e_date 		datetime
declare @LastMonth_Month	int
declare @LastMonth_Year		int
set @LastMonth_Month=5
set @LastMonth_Year=2008
set @s_date=dateadd(month,cast(@LastMonth_Month as int)-1,dateadd(year,cast(@LastMonth_Year as int)-1900,0))
set @e_date=dateadd(month,cast(@LastMonth_Month as int),dateadd(year,cast(@LastMonth_Year as int)-1900,0))

The above code gets me May 2008 as NOW.
@s_date='2008-05-01 00:00:00.000'
@e_date='2008-06-01 00:00:00.000'

I'm using the code below to get last year last month as THEN
@s_date-365.25 to get '2007-05-01 00:00:00.000' and
@e_date-365.25 to get '2007-06-01 00:00:00.000'

I use @s_date and @e_date roughly 80 times thoughout my statement to get:
5/1/2008 to 06/01/2008 NOW
5/1/2007 to 06/01/2007 THEN
01/01/2008 to 06/01/2008 YTD
05/01/2007 to 01/01/2008 LSTYR

Is using -365.25 not correct? If it's not have you a better solution.

CardGunner

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 09:25:19

use this to find the last year's date


select	start_date	= dateadd(month, datediff(month, 0, getdate()) - 13, 0),
	end_date	= dateadd(month, datediff(month, 0, getdate()) - 12, -1)

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-06-26 : 10:29:03

You can also simplify your logic for "NOW":


select	LastMonthStart		= dateadd(month,datediff(month,0,getdate())-1,0),
	CurrentMonthStart	= dateadd(month,datediff(month,0,getdate()),0)

Results:
LastMonthStart           CurrentMonthStart
-----------------------  ----------------------- 
2008-05-01 00:00:00.000  2008-06-01 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

cardgunner

326 Posts

Posted - 2008-06-26 : 11:00:55

Thanks for the reply I will try to simplifing my NOW.

However I'm using 5 for month and 2008 for year and I have to convert that to the getdate().

What I take from this is that I will have to add to my declare and variables

where it will be something like


declare @LastMonth_Month	int
declare @LastMonth_Year		int
declare @now_s_date 		datetime
declare @now_e_date 		datetime
declare @thn_s_date		datetime
declare @thn_e_date		datetime
declare @ytd_s_date		datetime
declare @ytd_e_date		datetime
declare @lyr_s_date		datetime
declare @lyr_e_date		datetime
select @LastMonth_Month=5
select @LastMonth_Year=2008
select @now_s_date=dateadd(month,cast(@LastMonth_Month as int)-1,dateadd(year,cast(@LastMonth_Year as int)-1900,0))
select @now_e_date=dateadd(month,cast(@LastMonth_Month as int),dateadd(year,cast(@LastMonth_Year as int)-1900,0))
select @thn_s_date=dateadd(month,cast(@LastMonth_Month as int)-1,dateadd(year,cast(@LastMonth_Year as int)-1901,0))
select @thn_e_date=dateadd(month,cast(@LastMonth_Month as int),dateadd(year,cast(@LastMonth_Year as int)-1901,0))
select @ytd_s_date=
select @ytd_e_date=
select @lyr_s_date=
select @lyr_e_date=

which you will notice I didn't put in tfor ytd and lyr but will.

I also went with select instead of set because I was told by a forum it was a better performer among other things.

However is this better to do it this way or to do 2 variables and change that variable to meet the different requirements.

I'm defining the code below is a variable


declare @now_s_date datetime
set @now_s_date=dateadd(month,cast(@LastMonth_Month as int)-1,dateadd(year,cast(@LastMonth_Year as int)-1900,0))

Is it better to have 10 variables in 80 places

or

4 variables altered in the statement in 80 places such as

(@now_s_date -365.25) for THEN where it would go like t_rsdt >= @now_s_date-365.25 ?

CardGunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources