Link to home
Start Free TrialLog in
Avatar of fahVB
fahVB

asked on

Previous month start and end dates

I have several update stmts where i am manually entering a previous month start and end date
e.g
and v.effective between '06/01/2008' and '06/30/2008'

i have this stmt which actually calls the current month start and end date,

Declare @StartDate datetime
declare @EndDate datetime
set @startdate = dateadd(dd,1 - datepart(dd,getdate()),getdate())
set @enddate = dateadd(dd,- datepart(dd,dateadd(mm,1,getdate())),dateadd(mm,1,getdate()))
select @StartDate, @EndDate

my question is how should i change the above so it calls previous month start and end dates, so i can just place this in my update stmts

e.g.
and v.effective between @StartDate and @EndDate

Thank you...




Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

You would just use -1 instead of 1 for the dateadd(mm, ...) statements.

ASKER CERTIFIED SOLUTION
Avatar of gigglick
gigglick

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fahVB
fahVB

ASKER

So, after declaring these variables, I should be able to use them in my 100's of update stmt's, Correct?
Avatar of fahVB

ASKER

Thank you...
Yes, that is the point of the variables.  Very simple and effective.
Avatar of fahVB

ASKER

Thank you...
Avatar of fahVB

ASKER

I am getting diff resluts when i use hard coded date verses variables

Declare @StartDate datetime
declare @EndDate datetime
set @startdate = dateadd(m,-1,dateadd(dd,1 - datepart(dd,getdate()),getdate()) )
set @enddate = dateadd(d,-1,dateadd(dd,1 - datepart(dd,getdate()),getdate()))
select count(*) from coopi
where (card_acceptor_term_id not like 'CK%' OR card_acceptor_term_id not like '82320%')
and local_txn_date between @StartDate and @EndDate
and transaction_amount <> 0
and tran_fee_amount <> 0

I get 22537


but when i use this
select count(*) from coopi
where (card_acceptor_term_id not like 'CK%' OR card_acceptor_term_id not like '82320%')
and local_txn_date between '6/1/2008' and '6/30/2008'
and transaction_amount <> 0
and tran_fee_amount <> 0

i get 23287


is there i am missing?



Remember that when you use getdate() that you get the time as well.  So you may want to do something like:

Declare @StartDate datetime
declare @EndDate datetime
declare @CurrentDate datetime
SET @CurrentDate = CONVERT(datetime, CONVERT(varchar, getdate(), 110))

set @startdate = dateadd(m,-1,dateadd(dd,1 - datepart(dd, @CurrentDate), @CurrentDate) )
set @enddate = dateadd(d,-1,dateadd(dd,1 - datepart(dd, @CurrentDate), @CurrentDate))

Then you will only get the actual date with 12:00 AM or 0:00
Avatar of fahVB

ASKER

Beautiful, thanks so much ...Is there a way to assign points to a closed question?