Link to home
Avatar of abarefoot
abarefoot

asked on

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))
Avatar of D B
D B
Flag of United States of America image

WHERE myDate >= DATEADD(DAY, 26, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)) AND myDate < CAST(DATEADD(dd,-(DAY(GETDATE())-26), GETDATE()) AS DATE)
Avatar of Eric Sherman
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
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of abarefoot
abarefoot

ASKER

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.
Perfect script and thanks for the tips!
A pleasure to be sure.
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?