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...




Microsoft SQL Server 2005

Avatar of undefined
Last Comment
fahVB

8/22/2022 - Mon
DBAduck - Ben Miller

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

ASKER CERTIFIED SOLUTION
gigglick

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
fahVB

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

Thank you...
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
DBAduck - Ben Miller

Yes, that is the point of the variables.  Very simple and effective.
ASKER
fahVB

Thank you...
ASKER
fahVB

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?



Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DBAduck - Ben Miller

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
ASKER
fahVB

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