[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Date Search between

Posted on 2007-08-11
7
Medium Priority
?
239 Views
Last Modified: 2013-11-06
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
Comment
Question by:dotnet0824
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:DropZone
ID: 19676138
-- @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
 

Author Comment

by:dotnet0824
ID: 19676279
Wow. that was a pretty fast answer. I will test it and get back to you today. Thanks again
0
 

Author Comment

by:dotnet0824
ID: 19676307
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 18

Expert Comment

by:DropZone
ID: 19676341
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
 
LVL 42

Expert Comment

by:dqmq
ID: 19676757
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
 
LVL 18

Accepted Solution

by:
DropZone earned 1000 total points
ID: 19676984
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
 

Author Comment

by:dotnet0824
ID: 19677525
Thanks a lot
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question