Loading ...

SQL: How to filter rows on the basis of weekday name | CodeAsp.Net

SQL: How to filter rows on the basis of weekday name

 /5
0 (0votes)

Few days back a person asked on the forums on how to filter rows based on weekday name i.e he was having "sunday","monday" etc as the input parameter and on the basis of that he wants to filter the rows which fall on that day. Below is the solution which I gave:

Let's create a user defined function "GetWeekDayNameOfParticularDate". This function accepts the date as input and will return the weekday name of that particular date. Next I chave created a sample table and inserted few records in it and used that user defined function to filter out the rows. Below is the script for it:

 

CREATE FUNCTION GetWeekDayNameOfParticularDate ( @Date DATETIME )
RETURNS NVARCHAR(20)
BEGIN

    DECLARE @Name NVARCHAR(20)

    SELECT  @Name = CASE ( DATEPART(dw, @Date) + @@DATEFIRST ) % 7
                      WHEN 1 THEN 'Sunday'
                      WHEN 2 THEN 'Monday'
                      WHEN 3 THEN 'Tuesday'
                      WHEN 4 THEN 'Wednesday'
                      WHEN 5 THEN 'Thursday'
                      WHEN 6 THEN 'Friday'
                      WHEN 0 THEN 'Saturday'
                    END

    RETURN @Name

END



GO

DECLARE @TABLE TABLE
    (
      [ID] INT IDENTITY,
      [DATETIMECOLUMN] DATETIME
    )

INSERT  INTO @TABLE
        SELECT  GETDATE()
        UNION ALL
        SELECT  DATEADD(DD, 1, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 2, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 3, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 4, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 5, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 6, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 7, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 8, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 9, GETDATE())
        UNION ALL
        SELECT  DATEADD(DD, 10, GETDATE())


SELECT  *
FROM    @TABLE
WHERE   dbo.GetWeekDayNameOfParticularDate([DATETIMECOLUMN]) = 'Sunday'

--OUTPUT
--ID          DATETIMECOLUMN
------------- -----------------------
--5           2010-08-22 22:52:13.400

 


That's it, above you saw that those records which fall on "Sunday" has been returned.

 

Do let me know your feedback, comments.

 

 

 

Comments (no comments yet)

Top Posts