locked
date BETWEEN query with a difference? RRS feed

  • Question

  •  

    Hi,

    I have a BETWEEN query (at least I think that's what it will need), but with a difference.

     

    Normally you would specific a field which was BETWEEN two set variables

     

    ie. {fieldname} BETWEEN 1 AND 3

     

     

    However I need mine the other way round.

     

    I have a series of records which have a startdate and enddate held against them.

     

    When a user submits a new record to the db, I need it to check that the starting and ending date range doesn't overlap any of the existing start-end date ranges that exist.

     

    In order to do that I'm trying to build a query which takes in the incoming startdate variable and see if that is within any of the existing start-date-enddate dates ranges of the existing records, and then same for the incoming endate. I actually want the ones that are going to cause a problem to appear...

     

    I;m sure there is a pretty easy way of coding this, but I'm struggling to get my head round it.

     

     

    Anyone offer any advice?
    Wednesday, December 12, 2007 6:05 PM

Answers

  • Well, that depends on how you save the information for the date. Using ISO Date without storing the time portion you could quickly use the following query without comverting the data types or chopping anything off.


    DECLARE @StartDate VARCHAR(8)
    DECLARE @EndDate VARCHAR(8)

    SET @StartDate = '20070102'
    SET @StartDate = '20070106'

    Select 'Something laps here'
    WHERE EXISTS
    (
     SELECT *
     FROM BillOfMaterials
     WHERE  (@StartDate < StartDate AND @EndDate > StartDate) OR
      (@EndDate > EndDate AND @StartDate > EndDate)

    )

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

     

    Friday, December 14, 2007 6:13 PM
  • I have a slight correction on the WHERE condition posted by Jens. The OP is looking for overlapping input @StartDate and @EndDate in the table.

    I think there can be 4 ways of overlapping.

    1. When input @StartDate and @EndDate is completly between StartDate and EndDate column.

    2. When StartDate and EndDate column is completly between @StartDate and @EndDate.

    3. When @StartDate is less than StartDate column and @EndDate between StartDate and EndDate column.

    4. When @StartDate is between StartDate & EndDate column and @EndDate is greater than EndDate column.


    Regards, RSingh

    Thursday, January 23, 2014 11:57 AM

All replies

  • You can try something like this

    use adventureworks

    go

    select birthdate from humanresources.employeetest where birthdate between

    (select min(birthdate) from humanresources.employee) and

    (select max(birthdate) from humanresources.employee)

     

    Thursday, December 13, 2007 10:58 AM
  • Well, that depends on how you save the information for the date. Using ISO Date without storing the time portion you could quickly use the following query without comverting the data types or chopping anything off.


    DECLARE @StartDate VARCHAR(8)
    DECLARE @EndDate VARCHAR(8)

    SET @StartDate = '20070102'
    SET @StartDate = '20070106'

    Select 'Something laps here'
    WHERE EXISTS
    (
     SELECT *
     FROM BillOfMaterials
     WHERE  (@StartDate < StartDate AND @EndDate > StartDate) OR
      (@EndDate > EndDate AND @StartDate > EndDate)

    )

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

     

    Friday, December 14, 2007 6:13 PM
  • I have a slight correction on the WHERE condition posted by Jens. The OP is looking for overlapping input @StartDate and @EndDate in the table.

    I think there can be 4 ways of overlapping.

    1. When input @StartDate and @EndDate is completly between StartDate and EndDate column.

    2. When StartDate and EndDate column is completly between @StartDate and @EndDate.

    3. When @StartDate is less than StartDate column and @EndDate between StartDate and EndDate column.

    4. When @StartDate is between StartDate & EndDate column and @EndDate is greater than EndDate column.


    Regards, RSingh

    Thursday, January 23, 2014 11:57 AM
  • select count(*) from demo1
    where '1/20/2014' > startdate and '1/20/2014'< enddate

    or

    where '1/21/2014' > startdate and '1/20/2014'< enddate

    ---------or------------------------------------

    select count(*) from demo1
    where '1/20/2014' between  startdate and enddate

    or

    where '1/21/2014' between  startdate and enddate

    ----------------------------------------------------------------------------

    where '1/20/2014' is the newstartdate

    and  '1/21/2014' is the new end date


    http://joeydj.com/



    Thursday, January 23, 2014 12:06 PM