SQL 2008 - Date Filter for TODAY and TODAY-30 Days
I am trying to get today only in my view. I have tried the following:
SELECT TOP (100) PERCENT DateCompleted
FROM dbo.tblSuperDateChecker
WHERE (DateCompleted = RTRIM(CONVERT(char(10), GETDATE(), 101)))
SELECT TOP (100) PERCENT DateCompleted
FROM dbo.tblSerialMasterPending
WHERE (DateCompleted = CONVERT(Varchar(8), GETDATE(), 112))
I have piece of data that should be showing: (here is date filter removed). The top line should display because today is the 10th
2017-03-10 13:45:16.567
2017-03-09 13:45:13.550
2017-03-09 13:45:10.540
SELECT DateCompleted
FROM dbo.tblSuperDateChecker
WHERE DateCompleted >= CAST(GETDATE() as date)
declare @today datetime
set @today = dateadd(dd, datediff(dd,0, getDate()), 0) -- this would be the normal line
SELECT top(1) t.DateCompleted, oa.P30DateCompleted
FROM tblSerialMasterPending t
OUTER APPLY(
select top(1) DateCompleted P30DateCompleted
from tblSerialMasterPending p30
where p30.DateCompleted >= dateadd(day,-30,@today) and p30.DateCompleted < dateadd(day,-29,@today)
order by p30.DateCompleted DESC
) oa
WHERE t.DateCompleted >= @today and t.DateCompleted < dateadd(day,30,@today)
ORDER BY t.DateCompleted DESC
+---------------------+---------------------+
| DateCompleted | P30DateCompleted |
+---------------------+---------------------+
| 10.03.2017 13:45:16 | 08.02.2017 13:14:15 |
+---------------------+---------------------+
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Thanks for using Experts Exchange.
Please provide your email to receive a sample view!
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.