How to filter and display MySQL information by - This Day, This Week, This Month etc.

[ilya Kraft]

Hello,

I am working on a "Top Ranked" feature for my website. I need to display articles that were top ranked, but I want to show results from This Day, This Week, This Month, All Time and I really have no idea on how to do it. I have a field named creation_date which contains information about time when article was posted. And I somehow need to select information from database where creation_date is equal to This Day, This_Month, This_Week etc.
I assume that I will select information from database like this:

SELECT * FROM articles WHERE creation_date = //What do I put here for different times? (day, week, Month, All time)//
 

Does anyone have an idea on how to achieve this?

[Jason Kibble]

Try something like this:

DECLARE @StartDate DATETIME, @EndDate DATETIME
 
SELECT
    @StartDate = 
    CASE WHEN 'Today' THEN CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME)
    CASE WHEN 'This Week' THEN DATEADD(dd, -1 * (DATEPART(dw, GETDATE()) - 1), CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
    CASE WHEN 'ThisMonth' THEN DATEADD(dd, -1 * (DATEPART(d, GETDATE()) - 1), CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
    ELSE '1/1/1900'
    END
 
SELECT
    @EndDate = 
    CASE WHEN 'Today' THEN DATEADD(d, 1, CAST(FLOOR(CAST(GetDate() AS FLOAT)) AS DATETIME))
    CASE WHEN 'This Week' THEN DATEADD(d, 7, DATEADD(dd, -1 * (DATEPART(dw, GETDATE()) - 1), CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))
    CASE WHEN 'ThisMonth' THEN DATEADD(m,1,DATEADD(dd, -1 * (DATEPART(d, GETDATE()) - 1), CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))
    ELSE '12/31/9999'
    END
 
SELECT * FROM articles WHERE creation_date >= @StartDate AND creation_date < @EndDate
 

[Rabbit]

SELECT *
FROM Articles
WHERE ArticleDate = GetDate()
ORDER BY Hits DESC;
 
SELECT *
FROM Articles
WHERE ArticleDate = WeekOfYear(GetDate())
     AND Year(ArticleDate) = Year(GetDate())
ORDER BY Hits DESC;
 
SELECT *
FROM Articles
WHERE ArticleDate = Month(GetDate())
     AND Year(ArticleDate) = Year(GetDate())
ORDER BY Hits DESC;