CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Suhas
Suhas

Reputation: 83

How to select data's from this today,week and month sperately?

I have a problem that is I am unable to resolve as of now.

I need to get the data of

this day, this week and this month

I have a table reminder where I want to select reminders according to following parameters.
1. Today 2. This Week 3. This Month

The column rdate having the date format in dd-mm-yyyy which is stored as nvarchar

For example

If I execute this weeks query I should get data starting from this week i.e.

If it is Friday I should get data from starting from Sunday to Saturday of that week

How can I get the data as mentioned above. I have searched a lot on internet but I didn't get the solution?

This is the query I have been trying

SELECT 
    * 
FROM 
    reminder 
WHERE 
    date > DATE_SUB(GETDATE(), INTERVAL 1 DAY) 
ORDER BY 
    rdate DESC;

Where I'm converting nvarchar to date format.

Upvotes: 0

Views: 850

Answers (3)

Maen Hasan
Maen Hasan

Reputation: 159

To my knowledge, SQL server internally deals with date format as MM/dd/yyyy.
Usually I prefer to save date as string in SQL table since it's easier for inserting and retrieving. For example, suppose that the column rdate is defined as follows in your table reminder:
[rdate] nvarchar NULL
Then you can customize the select statement for a week as follows:
"Select R.* From reminder R Where CAST(R.rdate as datetime) between '03/04/2011' AND '03/11/2011'"
And for 10 days as follows:
"Select R.* From reminder R Where CAST(R.rdate as datetime) between '03/04/2011' AND '03/14/2011'"
And so on. If this is not what you want, please provide more details about your requirements.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

If you can't change the columns data type to Date (or DateTime), you must convert it to date in the query.

Here is one way to get the data for today, this week and this month:

Get records from today:

SELECT * 
FROM reminder 
WHERE CONVERT(Date, [date], 105) = CAST(GETDATE() as date)
ORDER BY rdate DESC;

Get records from this week:

SELECT * 
FROM reminder 
WHERE DATEPART(WEEK, CONVERT(Date, [date], 105)) =  DATEPART(WEEK, GETDATE())
AND DATEPART(YEAR, CONVERT(Date, [date], 105)) =  DATEPART(YEAR, GETDATE())
ORDER BY rdate DESC;

Get records from this Month:

SELECT * 
FROM reminder 
WHERE DATEPART(MONTH, CONVERT(Date, [date], 105)) =  DATEPART(MONTH, GETDATE())
AND DATEPART(YEAR, CONVERT(Date, [date], 105)) =  DATEPART(YEAR, GETDATE())
ORDER BY rdate DESC;

Upvotes: 2

Serge
Serge

Reputation: 4036

If it's not possible to change the [date] column's data type to DATE, then you will incur a massive performance penalty when trying to filter by date.

Add computed column to table

We can add a computed column that will store the date in the correct format, and then index it for quick searchiing:

ALTER TABLE reminder
ADD Date_Value AS (CONVERT(DATE, '12-05-2016', 105)) PERSISTED;
-- This should yield superior performance
CREATE NONCLUSTERED INDEX IX_Date_Value ON reminder (Date_Value);

Table-valued function to calculate date range

Now, let's create an inline table-valued function to generate the date range for specific period types:

CREATE FUNCTION [dbo].[tvfn_Get_Date_Range](
    @Period_Type VARCHAR(100)
)
RETURNS
TABLE
AS RETURN
(
    WITH date_range AS(
            SELECT   CAST(GETDATE() AS DATE) d
                    -- This line works correctly if your week starts on Sunday
                    ,CAST(DATEADD(WEEK, DATEDIFF(WEEK, '19050101', GETDATE()), '19050101') AS DATE) AS week_start

                    ,CAST(DATEADD(DAY, - DAY(GETDATE()) + 1, GETDATE()) AS DATE) AS month_start
                    ,CAST(DATEADD(MONTH, 1, DATEADD(DAY, - DAY(GETDATE()), GETDATE())) AS DATE) AS month_end
    )
    SELECT   d AS From_Date
            ,d AS To_Date
    FROM date_range
    WHERE @Period_Type = 'DAY'

    UNION ALL

    SELECT   week_start
            ,DATEADD(DAY, 7, week_start)
    FROM date_range
    WHERE @Period_Type = 'WEEK'

    UNION ALL

    SELECT   month_start
            ,month_end
    FROM date_range
    WHERE @Period_Type = 'MONTH'

)

In the above function, week starts on Sunday. If you need this to be configurable, then take a look at the answer to SET DATEFIRST in FUNCTION.

Fast, simple querying now possible

You can now use the two together using a simple query:

SET @Range VARCHAR(100) = 'WEEK'

SELECT *
FROM reminder
CROSS APPLY [dbo].[tvfn_Get_Date_Range](@Range) dr
WHERE Date_Value BETWEEN dr.Date_From AND dr.Date_To

Upvotes: 2

Related Questions