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()),get date())
set @enddate = dateadd(dd,- datepart(dd,dateadd(mm,1,g etdate())) ,dateadd(m m,1,getdat e()))
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...
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()),get
set @enddate = dateadd(dd,- datepart(dd,dateadd(mm,1,g
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...
You would just use -1 instead of 1 for the dateadd(mm, ...) statements.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, after declaring these variables, I should be able to use them in my 100's of update stmt's, Correct?
ASKER
Thank you...
Yes, that is the point of the variables. Very simple and effective.
ASKER
Thank you...
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()),get date()) )
set @enddate = dateadd(d,-1,dateadd(dd,1 - datepart(dd,getdate()),get date()))
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?
Declare @StartDate datetime
declare @EndDate datetime
set @startdate = dateadd(m,-1,dateadd(dd,1 - datepart(dd,getdate()),get
set @enddate = dateadd(d,-1,dateadd(dd,1 - datepart(dd,getdate()),get
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
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
Beautiful, thanks so much ...Is there a way to assign points to a closed question?