Get results between 2 date fields?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after


    USE [testdb]
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[testTable](
     [recid] [int] IDENTITY(1,1) NOT NULL,
     [Date1] [smalldatetime] NULL,
     [Date2] [smalldatetime] NULL
    ) ON [PRIMARY]

    GO

     INSERT INTO testTable(date1,date2)
     VALUES('01/jan/2019','11/jan/2019')
     INSERT INTO testTable(date1,date2)
     VALUES('13/jan/2019','21/jan/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('01/feb/2019','15/feb/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('12/mar/2019','21/mar/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('09/apr/2019','15/apr/2019')

    so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
    and the todate of '21/mar/2019'
    if that is even possible ?

  • Neil Burton

    SSC-Insane

    Points: 21725

    mick burden - Tuesday, January 22, 2019 4:47 AM

    there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after


    USE [testdb]
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[testTable](
     [recid] [int] IDENTITY(1,1) NOT NULL,
     [Date1] [smalldatetime] NULL,
     [Date2] [smalldatetime] NULL
    ) ON [PRIMARY]

    GO

     INSERT INTO testTable(date1,date2)
     VALUES('01/jan/2019','11/jan/2019')
     INSERT INTO testTable(date1,date2)
     VALUES('13/jan/2019','21/jan/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('01/feb/2019','15/feb/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('12/mar/2019','21/mar/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('09/apr/2019','15/apr/2019')

    so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
    and the todate of '21/mar/2019'
    if that is even possible ?

    DECLARE @Date SMALLDATETIME = '14/mar/2019';

    SELECT
    Recid
    FROM testTable

    WHERE
        date1 <= @Date
    AND Date2 >= @Date; 

    That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help [/url]

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Neil Burton - Tuesday, January 22, 2019 5:14 AM

    mick burden - Tuesday, January 22, 2019 4:47 AM

    there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after


    USE [testdb]
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[testTable](
     [recid] [int] IDENTITY(1,1) NOT NULL,
     [Date1] [smalldatetime] NULL,
     [Date2] [smalldatetime] NULL
    ) ON [PRIMARY]

    GO

     INSERT INTO testTable(date1,date2)
     VALUES('01/jan/2019','11/jan/2019')
     INSERT INTO testTable(date1,date2)
     VALUES('13/jan/2019','21/jan/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('01/feb/2019','15/feb/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('12/mar/2019','21/mar/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('09/apr/2019','15/apr/2019')

    so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
    and the todate of '21/mar/2019'
    if that is even possible ?

    DECLARE @Date SMALLDATETIME = '14/mar/2019';

    SELECT
    Recid
    FROM testTable

    WHERE
        date1 <= @Date
    AND Date2 >= @Date; 

    That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

    That is brilliant, many thanks. Have a good day

    Mick

  • Neil Burton

    SSC-Insane

    Points: 21725

    You're welcome.  I should have asked a couple of questions though.  The first is whether your sample ranges are supposed to be non-contiguous?  12 Jan 2019 doesn't fall in any range you've provided.  Second, are your date1 and date2 columns inclusive?  I know you said 'between' but does that mean 'on or after' or 'after' and conversely is it 'on' or 'on or before?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help [/url]

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Neil Burton - Tuesday, January 22, 2019 6:44 AM

    You're welcome.  I should have asked a couple of questions though.  The first is whether your sample ranges are supposed to be non-contiguous?  12 Jan 2019 doesn't fall in any range you've provided.  Second, are your date1 and date2 columns inclusive?  I know you said 'between' but does that mean 'on or after' or 'after' and conversely is it 'on' or 'on or before?

    I'm not entirely sure what you are asking me here, all I can say is it works exactly how I want it too, so thanks again

    Mick

  • Neil Burton

    SSC-Insane

    Points: 21725

    mick burden - Wednesday, January 23, 2019 4:00 AM

    Neil Burton - Tuesday, January 22, 2019 6:44 AM

    You're welcome.  I should have asked a couple of questions though.  The first is whether your sample ranges are supposed to be non-contiguous?  12 Jan 2019 doesn't fall in any range you've provided.  Second, are your date1 and date2 columns inclusive?  I know you said 'between' but does that mean 'on or after' or 'after' and conversely is it 'on' or 'on or before?

    I'm not entirely sure what you are asking me here, all I can say is it works exactly how I want it too, so thanks again

    Mick

    If it does what you want then fair enough.  I'm just trying to make sure you don't get caught out by something you haven't thought of.  

    The first point is that you have gaps in your date ranges.  The first range runs from 01/Jan/19 to 11/Jan/19 but your second runs from the 13/Jan/19 until 21/Jan/19.  What happens if you get a date to test that falls on 12/Jan/19?  That's in neither range and won't return anything, is this right?

    The second question is, if the test date is 13/Jan/19 is that to be included in the second range or are you only interested in dates from 14/Jan/19 to 20/Jan/19?  I think you want to count the start and end dates but it's always better to check!


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help [/url]

  • Luis Cazares

    SSC Guru

    Points: 183524

    Neil Burton - Tuesday, January 22, 2019 5:14 AM

    mick burden - Tuesday, January 22, 2019 4:47 AM

    there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after


    USE [testdb]
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[testTable](
     [recid] [int] IDENTITY(1,1) NOT NULL,
     [Date1] [smalldatetime] NULL,
     [Date2] [smalldatetime] NULL
    ) ON [PRIMARY]

    GO

     INSERT INTO testTable(date1,date2)
     VALUES('01/jan/2019','11/jan/2019')
     INSERT INTO testTable(date1,date2)
     VALUES('13/jan/2019','21/jan/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('01/feb/2019','15/feb/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('12/mar/2019','21/mar/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('09/apr/2019','15/apr/2019')

    so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
    and the todate of '21/mar/2019'
    if that is even possible ?

    DECLARE @Date SMALLDATETIME = '14/mar/2019';

    SELECT
    Recid
    FROM testTable

    WHERE
        date1 <= @Date
    AND Date2 >= @Date; 

    That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

    You could also use BETWEEN to shorten the code.

    DECLARE @Date SMALLDATETIME = '20190314'; --This format is language settings independent.

    SELECT Recid 
    FROM testTable
    WHERE @Date BETWEEN date1 AND Date2; 

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Luis Cazares - Wednesday, January 23, 2019 8:48 AM

    Neil Burton - Tuesday, January 22, 2019 5:14 AM

    mick burden - Tuesday, January 22, 2019 4:47 AM

    there is 2 date fields with a from date and a to date, is it possible to find the record where if you pass in a date it works out which record it falls between? I'll put my sql below to help explain what I'm after


    USE [testdb]
    GO
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[testTable](
     [recid] [int] IDENTITY(1,1) NOT NULL,
     [Date1] [smalldatetime] NULL,
     [Date2] [smalldatetime] NULL
    ) ON [PRIMARY]

    GO

     INSERT INTO testTable(date1,date2)
     VALUES('01/jan/2019','11/jan/2019')
     INSERT INTO testTable(date1,date2)
     VALUES('13/jan/2019','21/jan/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('01/feb/2019','15/feb/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('12/mar/2019','21/mar/2019')
      INSERT INTO testTable(date1,date2)
     VALUES('09/apr/2019','15/apr/2019')

    so if I pass in a date such as 14/mar/2019 I would like it to return the recid field as my date fell in between the fromdate of '12/mar/2019'
    and the todate of '21/mar/2019'
    if that is even possible ?

    DECLARE @Date SMALLDATETIME = '14/mar/2019';

    SELECT
    Recid
    FROM testTable

    WHERE
        date1 <= @Date
    AND Date2 >= @Date; 

    That'll do it.  If you're only holding dates it makes more sense to use the DATE datatype rather SMALLDATETIME. 

    You could also use BETWEEN to shorten the code.

    DECLARE @Date SMALLDATETIME = '20190314'; --This format is language settings independent.

    SELECT Recid 
    FROM testTable
    WHERE @Date BETWEEN date1 AND Date2; 

    many thanks Luis

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply