asked on

SQL 2008 - Date Filter for TODAY and TODAY-30 Days

I have this date format in my view (table) : 2017-03-10 13:45:16.567

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

Jim Horn

CAST(GETDATE() as date) will return the date part of GETDATE() without the time.

SELECT DateCompleted
FROM dbo.tblSuperDateChecker
WHERE DateCompleted >= CAST(GETDATE() as date)

Open in new window

allenkent

ASKER

Perfect. Where do I put additional -30 days (today to 30 days ago)

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

here is another approach which allows the 30 days ago data to be presented on the same row as today

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

Open in new window

e.g.

+---------------------+---------------------+
|    DateCompleted    |  P30DateCompleted   |
+---------------------+---------------------+
| 10.03.2017 13:45:16 | 08.02.2017 13:14:15 |
+---------------------+---------------------+

Open in new window