SQL Date Script Help

I need a date script that starts on the 26th of previous month ends on 25th of current month.  Also I need this script to select those days no matter when the script is run since this script may or may not run on the same day.
Below is what I was going to use but the version of SQL I'm running doesn't support EOMONTH.  
WHERE
(MyTran.MyDate BETWEEN Dateadd(day, 26, EOMONTH(getdate(),-2)) AND Dateadd(day, 25, EOMONTH(getdate(),-1)))

--script for 26th of previous month
Select Dateadd(day, 26, EOMONTH(getdate(),-2))
--Script for 25th of current month

Thanks
Select Dateadd(day, 25, EOMONTH(getdate(),-1))
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbbishopCommented:
WHERE myDate >= DATEADD(DAY, 26, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) AND myDate < CAST(DATEADD(dd,-(DAY(GETDATE())-26), GETDATE()) AS DATE)
0
Eric ShermanAccountant/DeveloperCommented:
Another method ....

BETWEEN If(MONTH(CURDATE()) = 1, STR_TO_DATE(CONCAT_WS('-', YEAR(CURDATE())-1,12, 26), '%Y-%M-%D') AND STR_TO_DATE(CONCAT_WS('-', YEAR(CURDATE()),MONTH(CURDATE()), 25), '%Y-%M-%D'), STR_TO_DATE(CONCAT_WS('-', YEAR(CURDATE()),MONTH(CURDATE())-1, 26), '%Y-%M-%D') AND STR_TO_DATE(CONCAT_WS('-', YEAR(CURDATE()),MONTH(CURDATE()), 25), '%Y-%M-%D'));


ET
0
PortletPaulfreelancerCommented:
select
     --starts on the 26th of previous month 
   DATEADD(month,-1,DATEADD(day, - (DAY(getdate() ) - 1) + 24, cast(getdate() as date) ) )
   
    --ends on 25th of current month
 , DATEADD(day, - (DAY(getdate() ) - 1) + 25, cast(getdate() as date) ) 

;

Open in new window

If you need to strip the current time from getdate() I have cast getdate() to date

{+edit}By the way. I caution you not to use "between" for date range selections
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
Itzik Ben-Gan
also see: "Beware of Between"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

abarefootAuthor Commented:
dbbishop - I've already tried a script just like that and it changes depending on the date you run the script.  Not what I need.  I need it to pull the dates no matter what day you run the script.  thanks anyways.

etsherman - Sorry but that is complicated.  It may work but to complicated for me.  Thanks

Paul Maxwell - Thanks for the script.  That is exactly what I'm looking for.  Also thanks for the tip on not using between.  I'll make sure to change my ways for now on.
0
abarefootAuthor Commented:
Perfect script and thanks for the tips!
0
PortletPaulfreelancerCommented:
A pleasure to be sure.
0
dbbishopCommented:
I am not sure about you confusion of my script. The only issue with it is I think the start date is the 27th of previous month instead of 26th which is easily fixed by changing the first 26 to 25. It will use range of 26th of previous month to 25th of current month no matter what day of the month it is run. Did you test it?  What was the issue?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.