• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Date Search between

Search between dates  / given a date
===============================

. I have a   asp.net calendar control on the web form. if the user selects a day lets say  August 14th 2007(Tuesday)...
  I want to build a SQL Statement which  says "Select * from table where weekStartDate= August 13th and weekend date= August17th "   (August 13th - August17th)(Monday-Friday).... I want to bring a report for a Week (Mon-Friday) given a date selected by the user.
0
dotnet0824
Asked:
dotnet0824
  • 3
  • 3
1 Solution
 
DropZoneCommented:
-- @UserDate represents the date the user provided
SELECT * FROM tablename
WHERE datecolumn
    BETWEEN
        DATEADD(DD, 1 - DATEPART(DW, CONVERT(VARCHAR(10), @UserDate, 111)), CONVERT(VARCHAR(10), @UserDate, 111))
    AND
        DATEADD(DD, 7, DATEADD(DD, 1 - DATEPART(DW, CONVERT(VARCHAR(10), @UserDate, 111)), CONVERT(VARCHAR(10), @UserDate, 111)))

Basically, we find the first day of the week containing the date the user entered, and then we add 7 days to it to get the end of the week.

A better way is to create a stored procedure for this:

CREATE PROCEDURE SearchWeek(
    @UserDate   datetime
)
AS

    DECLARE
        @DatePart      datetime,
        @StartDate     datetime,
        @EndDate      datetime

    -- Get the date part only
    SET @DatePart = CONVERT(VARCHAR(10), @UserDate, 111)

    -- Get the start date
    SET @StartDate = DATEADD(DD, 1 - DATEPART(DW, @DatePart), @DatePart)

    -- Get the end date
    SET @EndDate = DATEADD(DD, 7, @StartDate)

    SELECT * FROM tablename
    WHERE datecolumn BETWEEN @StartDate AND @EndDate
END

See these pages for a more detailed explanation:
http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx
http://blog.sqlauthority.com/2007/06/08/sql-server-udf-function-to-display-current-week-date-and-day-weekly-calendar/

      -dZ.
0
 
dotnet0824Author Commented:
Wow. that was a pretty fast answer. I will test it and get back to you today. Thanks again
0
 
dotnet0824Author Commented:
but the user would select only one Date lets say 14th Aug which is Tuesday.  The week has to be calcualted from Monday - Saturday (13th -19th)... Somehow it has to know that  in the given UserDate the week starts from Monday (ie 13th and adds 7+ days) to 19th (Sunday)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DropZoneCommented:
That's what the code does:  It gets the day of the week from the date that the user entered, and from there computes when will Monday fall, then Sunday, and use those as the Start/End dates.

If you only want weekdays, then instead of adding 7, for the EndDate, add 5.

    -dZ.
0
 
dqmqCommented:
DropZone has a working idea, I just don't understand converting dates to varchars and I think it retuens Sunday through Thursday.  Isn't this simpler:

SELECT * FROM tablename
WHERE datecolumn
    BETWEEN
        DATEADD(DD, 2 - DATEPART(DW, @UserDate), @UserDate)  -- monday is day 2
    AND
        DATEADD(DD, 6 - DATEPART(DW, @UserDate), @UserDate)  --friday is day 6
     
0
 
DropZoneCommented:
First, I converted it to a varchar to extract the time part, to simplify the computations by ignoring the times.

Second, my code counted Sunday through saturday.  You just had to modify it to offset it to weekdays only:

CREATE PROCEDURE SearchWeek(
    @UserDate   datetime
)
AS

    DECLARE
        @DatePart      datetime,
        @StartDate     datetime,
        @EndDate      datetime

    -- Get the date part only
    SET @DatePart = CONVERT(VARCHAR(10), @UserDate, 111)

    -- Get the start date
    SET @StartDate = DATEADD(DD, 2 - DATEPART(DW, @DatePart), @DatePart)

    -- Get the end date
    SET @EndDate = DATEADD(DD, 5, @StartDate)

    SELECT * FROM tablename
    WHERE datecolumn BETWEEN @StartDate AND @EndDate
END

The problem of not removing the times is that instead of counting the entire day (from midnight to midnight), you may exclude records with times outside the one specified.  This is in the case that the date supplied as input contains a time part, such as when you use GETDATE().  However, if it is just the date with no time, then you can skip that part:

CREATE PROCEDURE SearchWeek(
    @UserDate   datetime
)
AS

    DECLARE
        @StartDate     datetime,
        @EndDate      datetime

    -- Get the start date
    SET @StartDate = DATEADD(DD, 1 - DATEPART(DW, @UserDate), @UserDate)

    -- Get the end date
    SET @EndDate = DATEADD(DD, 7, @StartDate)

    SELECT * FROM tablename
    WHERE datecolumn BETWEEN @StartDate AND @EndDate
END

Or in a single query:

SELECT * FROM tablename
WHERE datecolumn
    BETWEEN
        DATEADD(DD, 2 - DATEPART(DW, CONVERT(VARCHAR(10), @UserDate, 111)), CONVERT(VARCHAR(10), @UserDate, 111))
    AND
        DATEADD(DD, 6 - DATEPART(DW, CONVERT(VARCHAR(10), @UserDate, 111)), CONVERT(VARCHAR(10), @UserDate, 111))
0
 
dotnet0824Author Commented:
Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now